SQL Server's native support for date formatting and rounding is pathetic. SQL Server stores dates as 2 4-byte integers: one for the date and one for the time, and yet there's no way to extract the date or time portion--despite the datatype being set up in such a way to explicitly optimize for this operation!
Sick of reinventing the wheel, I finally broke down and wrote my own function to round dates to a time, day, week, month, quarter, or year:
CREATE FUNCTION fn_RoundDate(@Date DATETIME, @Format CHAR) RETURNS DATETIME
AS
BEGIN
IF @Date IS NULL RETURN NULL
RETURN CASE @Format
WHEN 'T' THEN -- Time
@Date - FLOOR(CAST(@Date AS FLOAT))
WHEN 'D' THEN -- Day
FLOOR(CAST(@Date AS FLOAT))
WHEN 'W' THEN -- Week
DATEADD(ww, DATEPART(ww, @Date) - 1, CONVERT(DATETIME, '1/1/' + CAST(YEAR(@Date) AS VARCHAR(4)), 101))
WHEN 'M' THEN -- Month
CONVERT(DATETIME, CAST(MONTH(@Date) AS VARCHAR(2)) + '/1/' + CAST(YEAR(@Date) AS VARCHAR(4)), 101)
WHEN 'Q' THEN -- Quarter
CONVERT(DATETIME, CAST(((MONTH(@Date) - 1) / 3) * 3 + 1 AS VARCHAR(2)) + '/1/' + CAST(YEAR(@Date) AS VARCHAR(4)), 101)
WHEN 'Y' THEN -- Year
CONVERT(DATETIME, '1/1/' + CAST(YEAR(@Date) AS VARCHAR(4)), 101)
ELSE -- Unknown format
@Date
END
END
Here is sample usage:
DECLARE @Date DATETIME;
SET @Date = '11/5/07 2:42 AM';
SELECT dbo.fn_RoundDate(@Date, 'T') AS Time,
dbo.fn_RoundDate(@Date, 'D') AS Day,
dbo.fn_RoundDate(@Date, 'W') AS Week,
dbo.fn_RoundDate(@Date, 'M') AS Month,
dbo.fn_RoundDate(@Date, 'Q') AS Quarter,
dbo.fn_RoundDate(@Date, 'Y') AS Year
With output as follows:
-
Time = 1900-01-01 02:42:00.000
-
Day = 2007-11-05 00:00:00.000
-
Week = 2007-11-05 00:00:00.000
-
Month = 2007-11-01 00:00:00.000
-
Quarter = 2007-10-01 00:00:00.000
-
Year = 2007-01-01 00:00:00.000
