Saturday, February 1, 2014

TSQL - Fun with Dates!

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.

Brenner Klenzman provided the formulas marked ** so that they would render correctly for GP data.

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 and additional notes. Also included, are links to articles about the new DateTime functions in SQL 2012!

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

Date

Code

Results on
02-05-2014

Current date and time 

SELECT Getdate()

 

2014-02-05 07:10:49.533

First day of current month

SELECT Dateadd(m,

Datediff(m, 0, Getdate()), 0)

2014-02-01 00:00:00.000

**Last day of current month

SELECT Dateadd(m, Datediff(m, 0, Getdate()) + 1, 0) - 1

 

2014-02-28 00:00:00.000

First day of previous month

SELECT Dateadd(m, -1, Dateadd(m, Datediff(m, 0,

Getdate ()), 0))

2014-01-01 00:00:00.000

Last day of  previous month

SELECT Dateadd(d, -1, Dateadd(m, Datediff(m, 0,

Getdate ()), 0))

2014-01-31 00:00:00.000

First day of  next month  

SELECT Dateadd(m, 1, Dateadd(m, Datediff(m, 0,

Getdate )), 0))

2014-03-01 00:00:00.000

Last day of next month  

SELECT Dateadd(d, -1, Dateadd(m, Datediff(m, 0

Dateadd (m, 2,Getdate())), 0))

2014-03-31 00:00:00.000

Last day of month 2  months ago

SELECT Dateadd(d, -1, Dateadd(m, Datediff(m, 0,

Dateadd(m, -1, Getdate())), 0))

 

2013-12-31 00:00:00.000

Last day of prev month in the previous year

SELECT Dateadd(d, -1, Dateadd(m, Datediff(m, 0,

Dateadd(m, -12, Getdate())), 0))

2013-01-31 00:00:00.000

Monday of current week

SELECT Dateadd(wk, Datediff(wk, 0, Getdate()), 0)

2014-02-03 00:00:00.000

Sunday of current week  

SELECT Dateadd(wk, Datediff(wk, 0, Getdate()), 6)

2014-02-09 00:00:00.000

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

2014-02-24 00:00:00.000

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

2014-02-13 00:00:00.000

 

First day of current year  

SELECT Dateadd(yy, Datediff(yy, 0, Getdate()), 0)

2014-01-01 00:00:00.000

**Last day of current year

SELECT Dateadd(yy, ( Datediff(yy, 0, Getdate())+ 1 ), 0) - 1

 

2014-12-31 00:00:00.000

First day of previous year  

SELECT Dateadd(yy, -1, Dateadd(yy, Datediff(yy, 0, Getdate()), 0))

 

2013-01-01 00:00:00.000

**Last day of previous year

SELECT Dateadd(yy, ( Datediff(yy, 0, Getdate())), 0) - 1

 

2013-12-31 00:00:00.000

First day of April in the current year  

SELECT Dateadd(m, 3, Dateadd(yy, Datediff(yy, 0, Getdate()), 0))

 

2014-04-01 00:00:00.000

 

First day of April in the previous year  

SELECT Dateadd(m, 3, Dateadd(yy, Datediff (yy, 0, Dateadd(yy, -1, Getdate())), 0))

 

2013-04-01 00:00:00.000

**

With thanks to Brenner Klenzman at Willoware for the correct statements.

 

Notes from Pinal Dave: All of the code above, (except Brenner’s, emphasis added) 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 this code before you rely on it.

Notes from Brenner Klenzman: Some standard SQL date functions can cause problems when used with GP data.

For example: On Feb 3rd of 2014, GETDATE()does not yield 02/03/2014, it actually includes the time down to milliseconds.  When executed, it yields:

2014-02-03 04:27:55.053

If you select documents >= getdate()-7, you are not going to get documents from 01/27/2014 because all documents in GP on that date actually have the date: 2014-01-27 00:00:00.000

Likewise, “Last day of current month” does not yield a GP-compatible “last day” if you use: select DATEADD(ms, -3, DATEADD(m, 0, DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0))) This formula yields:

2014-02-28 23:59:59.997

What you really want is:

2014-02-28 00:00:00.000

Additionally, if you have a query where 3 milliseconds is being subtracted, you get the same result when you subtract 2 milliseconds. For example: subtracting either 2 or 3 milliseconds yields 2013-12-31 24:59:59.997.  If you subtract 1 millisecond, you get 2014-01-01 00:00:00.000. The final digit in milliseconds is always 0, 3 or 7 (i.e. .880 or .883 or .887), you will never get 884. http://msdn.microsoft.com/en-s/library/ms187819.aspx

Thank you to Pinal Dave and Brenner Klenzman for information included in this post.

Boring notes about DateTime:

The SQL datetime data type is only accurate to 1/333 of a second (3.33ms) or 333 nanoseconds. A nanosecond is 1/100th of a second. A nanosecond is a unit known as a ‘tick’. A particular date is the number of ticks since 12:00 midnight on 1/1/0001 A.D.. The SQL DateTime’s accuracy is to the nearest increment of .000, .003 or .007 seconds. That’s why, in Brenner’s example, he got the same answer when he subtracted 2 milliseconds that he did when he subtracted 3 milliseconds.

SQL 2008 introduced the datetime2 data type. This data type is accurate to 1/100th of a second. Nevertheless, the datetime2 data type is not used in Dynamics GP.

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

2 comments:

Tim said...

My favorite new function in SQL Server 2012 is FORMAT because it functions like the Custom category in Excel Format Cells.

You can do things like FORMAT(SomeDateColumn,'MM/dd/yyyy') which would yield "07/04/2014"

FORMAT(SomeDateColumn,'MMMM d, yyyy') which would yield "July 4th, 2012"

FORMAT(SomeDateColumn,'dddd, MMMM d, yyyy') which would yield "Friday, July 4th, 2012"

Dynamics Confessor said...

I agree, a few of the new 2012 functions remind be of Excel. At last!

Leslie