I’m going to be writing a few posts about things I see people doing with date math, or as I’ve started calling it, “dath*”, that can really hurt performance.
Make Myself A Winner
As of this writing, if I run this query, I get these results:
SELECT SYSDATETIME() AS sys_date; sys_date 2019-11-08 17:22:10.6886473
It has a date part, and a time part. Right? Cool.
SELECT DATEDIFF(DAY, 0, SYSDATETIME()) AS [what does zero mean?], DATEDIFF(DAY, '19000101', SYSDATETIME()) AS [it means 1900-01-01];
Running these will both return the same value for me: 43,775. In other words, 0 and 1900-01-01 are interchangeable.
If you’re playing along at home, stick negative numbers in there too.
It might be a different result from mine when you run it, but they’ll both be the same locally.
To prove this out a little more: If we add 43,775 days to 0 (1900-01-01), we get today’s date (2019-11-08).
SELECT DATEADD( DAY, DATEDIFF(DAY, 0, SYSDATETIME()), 0 ) AS [add 43775 days to 1900-01-01];
In this example, I’m saying that I want to add the results of the datediff function to the date 1900-01-01.
These functions are pretty powerful, because they can accept expressions, literals, variables, parameters, column values, and so on.
The important part about this one is that the time is zeroed out.
What’s The Point?
I’ll often see people need to “flatten” dates to certain intervals.
By flatten, I mean the start of a day, week, month, or year, and likewise for the end values to all those intervals.
I’ve seen some really bad implementations of this. Most notable was probably a scalar valued function that converted a datetime to a 10 character string to remove the time portion.
In every where clause.
For much better implementations, check out these posts:
- Cheat sheet of important date calculations, by Tim Ford
- Calendar table of important dates, by Aaron Bertrand
- Simplifying Date Calculations, by Aaron Bertrand
Thanks for reading!
*I don’t really call it that don’t be ludicrous