clock_and_calendar_400_clr_9588

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

Here are some links to other helpful DateTime articles:

SQL datetime formatting function–convert to string

Part 1 Working with SQL Server DateTime Variables

Part 2 Displaying Dates and Times in Different Formats

Part 3 Searching for Particular Date Values and Ranges

Part 4 Date Math and Universal Time

SQL SERVER 2012 – Date and Time Functions

Until next post!

Leslie