Many times when working with date types in SQL we need to remove the time portion and leave just the date. There are a number of ways to accomplish this, but this article will focus on my favorite, the DATEADD/DATEDIFF (hereafter referred to as DADD) method.

For a long time, I knew about this method but I could never remember exactly where all the commas and zeroes and parens and everything went (I always had to look it up) and so I didn't always use it. Until I really sat down and looked at it and figured out how it worked, it was very hard to remember. With this article, I intend to explain the concept behind the method,give quite a few useful examples of how to apply it and a couple of the things to watch out for with it.

My most common usage involves stripping the time portion off of a day or GETDATE(). The DADD method of doing that is:

SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0). 

I'm writing this article on February 27, 2010 and as of right now,

GETDATE() = 2010-02-27 17:31:42.670
DADD: = 2010-02-27 00:00:00.000

While that may appear a bit bewildering at first, it's really quite basic. Let's break it down. First, take the inner DATEDIFF portion.

SELECT DATEDIFF(dd,0,GETDATE()) = 40234

What this portion is doing is figuring out the number of days that have passed between 0 (If you cast 0 as a date in SQL you get 01/01/1900) and today. That number is 40234.

The second portion of the equation is adding that number of days to 0. Because it is only adding the days and not the time portion, you get the very start of the day. With that in mind, it's a bit easier to remember the entire thing because you can just start from the datediff and add the dateadd. You're figuring out the number of days between 0 and today and then adding that back to 0.

DATEDIFF(dd,0,GETDATE()) -- Days between 0 and Today
DATEADD(dd, , 0) -- Add that number of days back to 0

The same concept works for many different time calculations. For instance, you can sub out Days for Week, month or Year:

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)
--: 2010-02-22 00:00:00.000 First day of the week.

SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0) 
--: 2010-02-01 00:00:00.000 First day of the month.

SELECT DATEADD(yy, DATEDIFF(yy,0,GETDATE()), 0) 
--: 2010-01-01 00:00:00.000 First day of the Year.

You can use a value other than zero in the dateadd portion to add or remove time. The below adds or removes 2 days. Because you're literally adding days, you don't need to worry about whether or not you cross over into a different month.

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 2) 
--: 2010-03-01 00:00:00.000 Start of the day 2 days from now

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -2)
--: 2010-02-25 00:00:00.000 Start of the day 2 days ago.

Note that if you change the values that are 0's in all of these, you are only offshifting by days. What if you want to add/subtract a week or month? You can do that by adding it right after the datediff portion.

SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE()) +2, 0) 
--: 2010-04-01 00:00:00.000 Start of the Month 2 Months from now.

In case you were wondering, this DOES work even if the days would have overflowed. For instance, adding a month to January 31 would still give you February.

SELECT DATEADD(mm, DATEDIFF(mm,0,'20100131') +1, 0) 
--: 2010-02-01 00:00:00.000 Start of next Month

You can use the 'first' theory to find the 'last' of something else. For example, if you wanted the last day of the prior month, you can start with the first day of the month and then subtract a day.

SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0) 
--: 2010-02-01 00:00:00.000 First day of the month.

SELECT DATEADD(dd,-1, DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)) 
--: 2010-01-31 00:00:00.000 Add -1 days (Subtract a day).

You could also have just added the number of months to -1 days, effectively subtracting a day here.

SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE()), -1) 
--: 2010-01-31 00:00:00.000 Add -1 days (Subtract a day).

When you go smaller than a day you can find the "end" of a day by using milliseconds* (SQL 2000/2005) or (if you use datetime2) microseconds/nanoseconds (SQL 2008). It is very important to note when doing this that the datetime data type is only accurate to 3 ms, not to 1. Subtracting 1 or 2ms here would do nothing as it would round back up to the start of the next day. With Datetime you will only ever have .990,.993 and .997 for ms. If you use datetime2 (available in SQL 2008), you can be accurate to 100 ns. By default though, GETDATE() resolves to a datetime data type and you have to cast/convert the value before you can use mcs or ns with dateadd.

SELECT DATEADD(ms,-3, DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)) 
--: 2010-02-26 23:59:59.997 End of the Previous Day(Datetime)

SELECT DATEADD(ns,-100,CAST(DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0) as datetime2)) 
--: 2010-02-26 23:59:59.9999999 End of the Previous Day (Datetime2)

While the above queries get you as close to the end of the day as possible for the appropriate types, it is usually advised that you avoid this all together whenever possible. For example, if you wanted all the values for today's date, instead of using >= the start of the day and <= the end of the day, it is recommended that you use >= the start of the day and < the start of tomorrow. This protects you in situations where the types can become more accurate than you were taking into account before. For instance, if datetime became accurate to 1ms (or the field was changed to a datetime2 field) and you were using this method, your queries would suddenly have the potential to miss data for just under 2ms each day.