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.