This was going to be a one liner but as ever, there's always something new to learn. Whenever I've needed to remove the time portion from a datetime in SQL Server I've generally used this SQL:
select convert(datetime, convert(char, getdate(), 106))
However, I found a faster method where no string conversion is necessary here. The performance improvement is only slight, 10k conversions are 0.21 seconds quicker, but it's interesting nonetheless.
Internally SQL Server stores datetime values as two 4-byte integers. The first one represents the days since 1st Jan. 1900 and the second one the number of milliseconds since midnight. Therefore another solution is to convert the datetime value to float, strip the digits behind the decimal point and convert it back to datetime again:
select cast(floor(cast(getdate() as float)) as datetime)
Captain Sybase ;)
Wednesday, November 01, 2006
Remove time from datetime in SQL Server
Subscribe to:
Post Comments (Atom)
3 comments:
aye aye captain!
This works, but it relies on the (undocumented, as far as I know)
conversion rules for datetime to float and float to datetime. Relying on
undocumented behaviour is allways dangerous.
Here's a safer way to trim the time from a date while still getting a
datetime result:
DATEADD(day, DATEDIFF(day, '20000101', someDate), '20000101')
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please note this is not my work, I only reference it here for completeness. The original can be found here -
http://www.issociate.de/board/post/275084/Simple_SQL_Query_(To_Neglect_time_from_DateTime_DataType).html
This is an even faster way to strip the time off your datetime:
select cast(dateDiff(d, 0, getDate()) as datetime)
Post a Comment