# How does date math work in SQL Server?

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?

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

``````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.