What is the best way to truncate a date in SQL Server?

Question

If I have a date value like 2010-03-01 17:34:12.018

What is the most efficient way to turn this into 2010-03-01 00:00:00.000?

As a secondary question, what is the best way to emulate Oracle's TRUNC function, which will allow you to truncate at Year, Quarter, Month, Week, Day, Hour, Minute, and Second boundaries?

Answer 1

To round to the nearest whole day, there are three approaches in wide use. The first one uses datediff to find the number of days since the 0 datetime. The 0 datetime corresponds to the 1st of January, 1900. By adding the day difference to the start date, you've rounded to a whole day;

select dateadd(d, 0, datediff(d, 0, getdate()))

The second method is text based: it truncates the text description with varchar(10), leaving only the date part:

select convert(varchar(10),getdate(),111)

The third method uses the fact that a datetime is really a floating point representing the number of days since 1900. So by rounding it to a whole number, for example using floor, you get the start of the day:

select cast(floor(cast(getdate() as float)) as datetime)

To answer your second question, the start of the week is trickier. One way is to subtract the day-of-the-week:

select dateadd(dd, 1 - datepart(dw, getdate()), getdate())

This returns a time part too, so you'd have to combine it with one of the time-stripping methods to get to the first date. For example, with @start_of_day as a variable for readability:

declare @start_of_day datetime
set @start_of_day = cast(floor(cast(getdate() as float)) as datetime)
select dateadd(dd, 1 - datepart(dw, @start_of_day), @start_of_day)

The start of the year, month, hour and minute still work with the "difference since 1900" approach:

select dateadd(yy, datediff(yy, 0, getdate()), 0)
select dateadd(m, datediff(m, 0, getdate()), 0)
select dateadd(hh, datediff(hh, 0, getdate()), 0)
select dateadd(mi, datediff(mi, 0, getdate()), 0)

Rounding by second requires a different approach, since the number of seconds since 0 gives an overflow. One way around that is using the start of the day, instead of 1900, as a reference date:

declare @start_of_day datetime
set @start_of_day = cast(floor(cast(getdate() as float)) as datetime)
select dateadd(s, datediff(s, @start_of_day, getdate()), @start_of_day)

To round by 5 minutes, adjust the minute rounding method. Take the quotient of the minute difference, for example using /5*5:

select dateadd(mi, datediff(mi,0,getdate())/5*5, 0)

This works for quarters and half hours as well.