A Quick Primer On Date Math In SQL Server

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:

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.

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

For instance:

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:

• 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

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.