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 |
| 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
- DATEFROMPARTS (year, month, day)
- DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)
- DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)
- DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)
- SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)
- TIMEFROMPARTS (hour, minute, seconds, fractions, precision)
- EOMONTH (start_date)
Until next post!
Leslie
2 comments:
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"
I agree, a few of the new 2012 functions remind be of Excel. At last!
Leslie
Post a Comment