Datetime Ceiling Function. Rounding Dates and Time
I've got a bit of a weird situation with the statistics generated by the software of one of our vendors (stored in mysql). The way that they aggregate minute statistics into hourly statistics is that
1:00:01 AM to 2:00:00 AM is rolled into the hour 2:00:00 AM. I need to be able to group their minute stats into hourly stats the way that they do, except in SQL server, there is no pre-existing Datetime ceiling function.
I never was able to find one that I really liked using Google, so I wrote my own using the datetime truncate functions I've found as a baseline.
I've come up with the following solution in a deterministic inline table valued function , which I think works well, but I'd like to ask the experts to pick at it to see if there's something I'm doing that irks them in some way, or possible a case where this function fails to produce the correct result.
--Included so you can see what I started with.
CREATE FUNCTION [dbo].[ufn_DateTimeFloor]
(
@Input datetime
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT (DATEADD(day, DATEDIFF(day, StaticDate, @Input), StaticDate)) AS Day_Floor
,(DATEADD(hour, DATEDIFF(hour, StaticDate, @Input), StaticDate)) AS Hour_Floor
,(DATEADD(minute, DATEDIFF(minute, StaticDate, @Input), StaticDate)) AS Minute_Floor
,(DATEADD(second, DATEDIFF(second, StaticDate, @Input), StaticDate)) AS Second_Floor
FROM (SELECT CONVERT([datetime], '1975-01-01T00:00:00.000', (126)) AS StaticDate) SubVars
)
CREATE FUNCTION [dbo].[ufn_DateTimeCeiling]
(
@Input datetime
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT (DATEADD(day, DATEDIFF(day, StaticDate, Adjusted_Day), StaticDate)) AS Day_Ceiling
,(DATEADD(hour, DATEDIFF(hour, StaticDate, Adjusted_Hour), StaticDate)) AS Hour_Ceiling
,(DATEADD(minute, DATEDIFF(minute, StaticDate, Adjusted_Minute), StaticDate)) AS Minute_Ceiling
,(DATEADD(second, DATEDIFF(second, StaticDate, Adjusted_Second), StaticDate)) AS Second_Ceiling
FROM (SELECT CONVERT([datetime], '1975-01-01T00:00:00.000', (126)) AS StaticDate
--datetime data type is accurate to 3.33 milliseconds, Per BOL.
,DATEADD(millisecond, -3.33, DATEADD(day, 1, @Input)) AS Adjusted_Day
,DATEADD(millisecond, -3.33, DATEADD(hour, 1, @Input)) AS Adjusted_Hour
,DATEADD(millisecond, -3.33, DATEADD(minute, 1, @Input)) AS Adjusted_Minute
,DATEADD(millisecond, -3.33, DATEADD(second, 1, @Input)) AS Adjusted_Second
) SubVars
)
iTVFs don't allow you to declare variables to be used inside the function, so I put the "variables" I'd like to use in the table FROM clause. This is another thing I'd like to have the experts pick at. Is this something I should avoid doing?
As far as performance goes, applying this TVF seems to have little if any affect on the performance of large queries (2.5 million rows). Aggregation using Day_Ceiling on same sample data was able to summarize my results in less than 2 seconds after a DBCC dropcleanbuffers and DBCC freeproccache.
Opinions? Suggestions? Questions?