How does date math work in SQL Server?

Question

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?

Answer 1

The best way to understand how date math works is to break a query down into its parts.

Start with this:

SELECT GETUTCDATE() AS utc_date

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.

   SELECT DATEDIFF(DAY, 0, GETUTCDATE()) AS [what does zero mean?],
          DATEDIFF(DAY, '19000101', GETUTCDATE()) AS [it means 1900-01-01]

Which means that you can convert nearly any number to a date. Even negative numbers.

   SELECT CONVERT(DATETIME, -1) AS [how is -1 a date?]

This will bring back 1899-12-31 00:00:00.000, which is the same thing as this:

SELECT DATEADD(DAY, -1, '19000101') AS [zero minus 1 days]

Just subtracting one day from 1900-01-01. Weird, right?

How does this help us?

Let's look inside our original query:

SELECT DATEDIFF(DAY, 0, GETUTCDATE())

That gives us the number of days between 1900-01-01 and the current date. Which means the full expression:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), 0)

Is the adding the number of days between 1900-01-01 and current to 1900-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.

DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), -30);

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.