A Quick Primer On Date Math

Come Up

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.

To get you ready for those, I need to talk about how functions like DATEADD and DATEDIFF work. It seems like that’s missing knowledge in many places.

Make Myself A Winner

As of this writing, if I run this query, I get these results:

It has a date part, and a time part. Right? Cool.

The first thing I need to show you is that with the DATEADD and DATEDIFF functions, some dates and numbers are interchangeable.

For instance:

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

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:

Thanks for reading!

*I don’t really call it that don’t be ludicrous

10 thoughts on “A Quick Primer On Date Math”

Leave a Reply

Your email address will not be published. Required fields are marked *