I often see queries written with DATEADD
and DATEDIFF
in the WHERE
clause to define a range, flatten datetime to 0 hours, or find the last day of a month or year, but I don't understand how all the parts work.
For example, this will find dates from the start of the current day, to the start of the the day 30 days ago.
SELECT *
FROM tbl
WHERE datecol >= DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), 0)
AND datecol < DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), -30);
What do all the different parts of this accomplish?
Best Answer
The best way to understand how date math works is to break a query down into its parts.
Start with this:
On this particular day, it returns
2017-10-04 19:34:20.050
.In date math, the number 0 and the date 1900-01-01 are interchangeable.
Which means that you can convert nearly any number to a date. Even negative numbers.
This will bring back
1899-12-31 00:00:00.000
, which is the same thing as this:Just subtracting one day from 1900-01-01. Weird, right?
How does this help us?
Let's look inside our original query:
That gives us the number of days between
1900-01-01
and the current date. Which means the full expression:Is the adding the number of days between
1900-01-01
and current to1900-01-01
. That gives us the very start of the current day, at 0 hours.The second part does the same thing, except subtracting 30 days from
1900-01-01
, at 0 hours.If this is all too much to remember, I totally understand.
For a cheat sheet of important date calculations, see Tim Ford's article.
For a calendar table of important dates, see Aaron Bertrand's article.