Remove time from date/time stamp in SQL Server

Posted by Dan on Dec 1, 2008 @ 10:30 AM

I thought I've blogged this tip before, but I saw someone else mention it on a mailing list this morning. If you ever want to remove the time portion in a MSSQL Server, you can do this by removing the decimal portion of the date/time stamp.

SQL Server stores date/times as a numeric value where the integer portion of the value represents the number of days since epoch and the time is represented by the decimal portion of the value. This allows us to cast a datetime field as a float and then round down to the nearest integer:

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

We first convert the datetime to a float:

cast(getutcdate() as float)

We next use the floor() function to round down to the nearest integer:

floor(cast(getutcdate() as float)

Finally, we convert the value back to a datetime object:

cast(floor(cast(getutcdate() as float)) as datetime)

This allows us to obtain all the records created by on the current date by doing something like:

select
    *
from
    Table
where
    cast(floor(cast(CreateDate as float)) as datetime) = cast(floor(cast(getutcdate() as float)) as datetime)

Since the above code does require an operation be done on each CreateDate record, depending on your schema and version of SQL Server, you may get better results by using:

select
    *
from
    Table
where

    CreateDate >= cast(floor(cast(getutcdate() as float)) as datetime)
and
    CreateDate < cast(floor(cast(dateadd(d, 1, getutcdate()) as float)) as datetime)

In SQL Server 2005 the execution plan for the two queries are equal, so they should be interchangeable.

Categories: SQL

7 Comments

  • al's Gravatar
    al
    thank-you - nice and simple - I really dislike SQL date-handling.
  • Salvador's Gravatar
    Salvador
    Thank you, really simple and elegant
  • Kevin's Gravatar
    Kevin
    Thanks, nice tip! But is it easier to just cast the date directly to int?

    SELECT Cast(Cast(GetDate() as int) as datetime)

    In my SQL Server instance, it handled it just like a cast to a float followed by a floor function. Is that behavior instance-specific?
  • Dan G. Switzer, II's Gravatar
    Dan G. Switzer, II
    @Kevin:

    I seem to recall experiencing some rounding issues casting directly to an INT--but I won't swear by it.
  • Kevin's Gravatar
    Kevin
    Dan, I did some research... you are right, there are rounding issues casting directly to int, but only with dates! This question has been batted around the SQL forums for awhile. Dates are rounded up in the afternoon and down in the morning.

    declare @x decimal(15,10);
    set @x = 11.9999999999;
    select cast(@x as int); -- truncates

    declare @y float;
    set @y = 11.9999999999;
    select cast(@y as int); -- truncates

    declare @y real;
    set @y = 11.99999;
    select cast(@y as int); -- truncates

    declare @x datetime
    set @x = '2009-10-21 12:01:00';
    select cast(cast(@x as int) as datetime) -- rounds!!
  • Dan G. Switzer, II's Gravatar
    Dan G. Switzer, II
    @Kevin:

    Thanks for adding this info--it'll prove useful to those who find this blog entry in the future.

    You probably already saw it, but you might take a look at my other blog entry that details more date/time tricks:

    http://blog.pengoworks.com/index.cfm/2009/1/9/Usef...
  • Ike's Gravatar
    Ike
    select cast(convert(varchar(10), getdate(), 101) as datetime)

Comments for this entry have been disabled.