TSQL - Fun with Dates!

Views (1857)

L Vail

65,271

Like(0)

Report

Are you constantly needing to come up with different relative dates in reports, SmartLists and the like? I found this great list of date calculations from Pinal Dave and Vivek Jamwal. I’m duplicating it here because 1) I’ll be able to find it, and 2) I wanted to share my find with the Dynamics GP community.

Please note: some of the formulas in the table can be replaced using the new functions in SQL 2012 – links below table.

At the end of this post, I have included several links to various DateTime related articles. Included are articles about the new DateTime functions in SQL 2012!

Enjoy, and thanks again to Pinal Dave and Vivek Jamwal.

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