Remove time from date/time stamp in SQL Server
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:
We first convert the datetime to a float:
We next use the floor() function to round down to the nearest integer:
Finally, we convert the value back to a datetime object:
This allows us to obtain all the records created by on the current date by doing something like:
*
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:
*
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.
15310 Views
Send
Print
Comments (7)
Comments
thank-you - nice and simple - I really dislike SQL date-handling.
Thank you, really simple and elegant
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?
@Kevin:I seem to recall experiencing some rounding issues casting directly to an INT--but I won't swear by it.
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!!
@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...
select cast(convert(varchar(10), getdate(), 101) as datetime)