Date Manipulation with DATEADD/DATEDIFF

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.670DADD: = 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 TodayDATEADD(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.

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.

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.

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.