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

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?

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

Solution 2:

If you are using SQL Server 2008, you can use the new Date datatype like this:

select cast(getdate() as date)

If you still need your value to be a DateTime datatype, you can do this:

select cast(cast(getdate() as date) as datetime)

A method that should work on all versions of SQL Server is:

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

Solution 3:

Try:

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

UPDATE: answer on the second question: for years you could use a little bit modified version of my answer:

SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)

for quarter:

SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)

and so on.

I checked, up to minutes - it's OK. But on seconds I've got an overflow message:

Difference of two datetime columns caused overflow at runtime.

One more update: take a look to the following answer to the same question

Solution 4:

This is late, but will produce the exact results requested in the post. I also feel it is much more intuitive than using dateadd, but that is my preference.

declare @SomeDate datetime = '2010-03-01 17:34:12.018'
SELECT 
 DATEFROMPARTS(
     YEAR(@SomeDate)
    ,MONTH(@SomeDate)
    ,'01'
    ) AS CUR_DATE_FROM_PARTS
,DATETIMEFROMPARTS(
     YEAR(@SomeDate)                     
    ,MONTH(@SomeDate)                
    ,'01' --DAY(@SomeDate)                   
    ,'00' --DATEPART(HOUR,@SomeDate)         
    ,'00' --DATEPART(MINUTE,@SomeDate)       
    ,'00' --DATEPART(SECOND,@SomeDate)       
    ,'00' --DATEPART(MILLISECOND,@SomeDate) 
    ) AS CUR_DATETIME_FROM_PARTS
,@SomeDate                         AS CUR_DATETIME
,YEAR(@SomeDate)                   AS CUR_YEAR
,MONTH(@SomeDate)                  AS CUR_MONTH
,DAY(@SomeDate)                    AS CUR_DAY
,DATEPART(HOUR,@SomeDate)          AS CUR_HOUR
,DATEPART(MINUTE,@SomeDate)        AS CUR_MINUTE
,DATEPART(SECOND,@SomeDate)        AS CUR_SECOND
,DATEPART(MILLISECOND,@SomeDate)   AS CUR_MILLISECOND
FROM Your_Table

Truncated Date: 2010-03-01

Truncated DateTime: 2010-03-01 00:00:00.000

DateTime: 2010-03-01 17:34:12.017