Introduction
I’ve often needed to seperate out the date or time components from a SQL datetime field. Traditionally, I and many colleagues have converted the datetime to a varchar, used string functions to parse out the desired component, and then converted the string back to a datetime. This approach has some serious issues:
- The string format for a datetime depends on the culture of the SQL Server excuting the query or function
- There is a prety big performance hit doing a lot of string parsing; you don’t want to have this in a sub query that generates a lot of rows or as part of a join expression
A new approach
Basically, a datetime field is stored internally as a float (double precision floating point number). The date part is everything to the left of the decimal point (the integer or whole number part). The time component is everything to the right of the decimal point (the fractional part).
Here’s an example: 38351.602349537 = 1/1/2005 2:27:23 PM
Stripping a off a time component
SELECT CONVERT (datetime, CONVERT (int, CONVERT (float, CONVERT (datetime, [DateToStrip])) * 10) / 10)
CREATE FUNCTION dbo.StripTime (@DateToStrip DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN CONVERT (DATETIME, CONVERT (INT, CONVERT (FLOAT, CONVERT (DATETIME, @DateToStrip)) * 10) / 10)
END
To remove the time component, we cast the datetime as a float, cast as an integer to strip off the fractional component, then cast back to a datetime. This is orders of magnitudes faster than converting to a varchar and using string operations to strip off the time component.
We multiply the float version of the date by 10 and then divide the integer version by 10 to simulate a conversion to an integer without rounding. Otherwise, datetimes that have a 12:00 PM or later time (represented as .5 to .99… in the decimal component) will be converted to a date for tomorrow when the time is stripped off.
Stripping off a date component
SELECT CONVERT (datetime, (1 – (CONVERT (float, [DateToStrip]) – CONVERT (int, CONVERT (float, [DateToStrip])))) * – 1 – 1)
CREATE FUNCTION dbo.StripDate (@DateToStrip DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN CONVERT (DATETIME, (1 – (CONVERT (FLOAT, @DateToStrip) – CONVERT (INT, CONVERT (FLOAT, @DateToStrip)))) * – 1 – 1)
END
To remove the date component, we need to get just the floating point part. So, we subtract the integer portion of the float from the whole float. This leaves us with just the decimal component. Next, we need to do some bit shifting, so we subtract the float component from 1. This leaves us with a the correctr bits, except for the sign bit, so we multiply by -1. All that’s left is to set the integer part to 1, so we subtract another 1 from the result.
To avoid the *10/10 step and the additional conversion to an int, I’ve found that this works just as well:
cast(floor(cast(@dateToStrip as float)) as datetime)
An even faster way is to use the ROUND() function properly to preserve times after 12:00 PM. Simply do the following:
CONVERT(datetime,(CONVERT(int,ROUND(CONVERT(float,@dateToStrip),0,1))
Sorry, got trigger happy. The previous is missing it’s last two ‘)’. The real string should be:
CONVERT(datetime,(CONVERT(int,ROUND(CONVERT(float,@dateToStrip),0,1))))
convert(char(8),@date,108)
+ culture independent
+ single step
+ readable
+ no manual parsing
– MS TransactSQL only
– never tested performance
Will, that is indeed an elegant way to do it. However, it does involve converting the date to text (not too bad), and then converting the text back to a date (a relatively slow operation), assuming you need to use the results as a native date-time data type.
This comment has been removed by a blog administrator.