Coding specific dates in SQL Server

Views (2538)

Victoria Yudin

22,769

Like(0)

Report

Quite often when writing reports I need to write a formula to calculate dates. Dates are not very straightforward to code in SQL Server, and I have started keeping a list of various formulas so that I do not have to re-write the code every time. I have previously posted some code on calculating date differences in SQL, however below is some code for getting specific dates.

DATE	CODE	RESULT ON 09/13/2010
Current date (and time)	`select GETDATE()`	09/13/2010
First day of current month	`select DATEADD(m,DATEDIFF(m,0,GETDATE()),0)`	09/01/2010
Last day of current month	`select DATEADD(ms,-3,DATEADD(m,0,DATEADD(m,DATEDIFF(m,0,GETDATE())+1,0)))`	09/30/2010
First day of previous month	`select DATEADD(m,-1,DATEADD(m,DATEDIFF(m,0,GETDATE()),0))`	08/01/2010
Last day of previous month	`select DATEADD(d,-1,DATEADD(m,DATEDIFF(m,0,GETDATE()),0))`	08/31/2010
First day of next month	`select DATEADD(m,1,DATEADD(m,DATEDIFF(m,0,GETDATE()),0))`	10/01/2010
Last day of next month	`select DATEADD(d,-1,DATEADD(m,DATEDIFF(m,0,DATEADD(m,2,GETDATE())),0))`	10/31/2010
Last day of month 2 months ago	`select DATEADD(d,-1,DATEADD(m,DATEDIFF(m,0,DATEADD(m,-1,GETDATE())),0))`	07/31/2010
Last day of previous month in the previous year	`select DATEADD(d,-1,DATEADD(m,DATEDIFF(m,0,DATEADD(m,-12,GETDATE())),0))`	08/31/2009
Monday of current week	`select DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)`	09/13/2010
Sunday of current week	`select DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)`	09/19/2010
Fourth Monday of current month	`select case when (2-DATEPART(dw,(DATEADD(m,DATEDIFF(m,0,GETDATE()),0)))) >=0 then DATEADD(wk,3,(DATEADD(m,DATEDIFF(m,0,GETDATE()),0))+(2-DATEPART(dw,(DATEADD(m,DATEDIFF(m,0,GETDATE()),0))))) else DATEADD(wk,4,(DATEADD(m,DATEDIFF(m,0,GETDATE()),0))+(2-DATEPART(dw,(DATEADD(m,DATEDIFF(m,0,GETDATE()),0))))) end`	09/27/2010
Second Thursday of current month	`select case when (5-DATEPART(dw,(DATEADD(m,DATEDIFF(m,0,GETDATE()),0)))) >=0 then DATEADD(wk,1,(DATEADD(m,DATEDIFF(m,0,GETDATE()),0))+(5-DATEPART(dw,(DATEADD(m,DATEDIFF(m,0,GETDATE()),0))))) else DATEADD(wk,2,(DATEADD(m,DATEDIFF(m,0,GETDATE()),0))+(5-DATEPART(dw,(DATEADD(m,DATEDIFF(m,0,GETDATE()),0))))) end`	09/09/2010
First day of current year	`select DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)`	01/01/2010
Last day of current year	`select DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))`	12/31/2010
First day of previous year	`select DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))`	01/01/2009
Last day of previous year	`select DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))`	12/31/2009
First day of April in the current year	`select DATEADD(m,3,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))`	04/01/2010
First day of April in the previous year	`select DATEADD(m,3,DATEADD(yy,DATEDIFF(yy,0,DATEADD(yy,-1,GETDATE())),0))`	04/01/2009

A few notes:

All of the code above uses GETDATE(), or the current date, as a starting point. To test these, just copy what’s in the CODE column above into a SQL query and execute it. If you need to use a parameter instead, you can replace GETDATE() with your parameter name.
Be careful with the calculations for weeks. The examples above are assuming that the first day of the week is Sunday. You can check whether this is the same on your SQL Server by running the following query:
select @@datefirst
If it returns 7, then your first day of the week is Sunday.
Don’t forget to test. While I have tested all the code above, I usually test with the current date and a few other dates in the surrounding months or years. Always test other people’s code before you rely on it.

Thank you to Pinal Dave for some of this information.

Filed under: GP Reports code, SQL coding Tagged: GP Reports code, SQL code, SQL Server