Order in Chaos

Often times you may want to get the floor of a specific date. For example, you may want to see everything that has happened on your system since the beginning of today, or perhaps between the start of yesterday and the start of today. For simple queries, this is usually done by entering in a datetime manually. For example
[cc lang="tsql"]
SELECT * FROM fooTable WHERE [createdate] >= ‘2009-11-06′ AND [createdate] < '2009-11-07'
[/cc]
However, it's often the case that punching in manual values like this is just not practical. It'd be nice if you could just give it any date and it would find the floor of the day.

You can do this fairly easily by taking advantage of T-SQL's DATEADD() and DATEDIFF() functions. To find the floor of a given datetime, do something similar to
[cc lang="tsql"]
SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),0)
[/cc]
This will give you the floor of today. Replace GETDATE() with any datetime, including a variable, to get the floor of that day. This will return something of the form '2009-11-07 00:00:00' Additionally, replace dd with hh or mi for the floor of the hour or minute.

Based on the code above, I use the function below to simplify getting the floors of various time periods.
[cc lang="tsql"]
CREATE FUNCTION fn_gettimefloor
(
@format VARCHAR(16),
@datetime DATETIME
)
RETURNS DATETIME
AS
BEGIN
IF @format in ('yy','yyyy','year')
RETURN DATEADD(yy, DATEDIFF(yy, 0, @datetime),0)
ELSE IF @format in ('qq','q', 'quarter')
RETURN DATEADD(qq, DATEDIFF(qq, 0, @datetime),0)
ELSE IF @format in ('mm','m','month')
RETURN DATEADD(mm, DATEDIFF(mm, 0, @datetime),0)
ELSE IF @format in ('dd','d','day')
RETURN DATEADD(dd, DATEDIFF(dd, 0, @datetime),0)
ELSE IF @format in ('wk','ww','week')
RETURN DATEADD(wk, DATEDIFF(wk, 0, @datetime),0)
ELSE IF @format in ('hh','hour')
RETURN DATEADD(hh, DATEDIFF(hh, 0, @datetime),0)
ELSE IF @format in ('mi', 'n','minute')
RETURN DATEADD(mi, DATEDIFF(mi, 0, @datetime),0)
ELSE IF @format in ('ss','s','second')
RETURN DATEADD(ss, DATEDIFF(ss, 0, @datetime),0)
ELSE IF @format in ('ms', 'millisecond')
RETURN DATEADD(ms, DATEDIFF(ms, 0, @datetime),0)
ELSE IF @format in ('mcs','microsecond')
RETURN DATEADD(mcs, DATEDIFF(mcs, 0, @datetime),0)
ELSE IF @format in ('ns','nanosecond')
RETURN DATEADD(ns, DATEDIFF(ns, 0, @datetime),0)
RETURN GETDATE()
END
GO
[/cc]
The above function can then be used in a way similar to the below example
[cc lang="tsql"]
select dbo.fn_gettimefloor('dd',GETDATE()) -- The floor of today
select dbo.fn_gettimefloor('hh',@someDateVar) -- The floor of a datetime passed into the function
select dbo.fn_gettimefloor('hh',DATEADD(hh, -5, GETDATE())) -- Get hour-floor of 5 hours ago
[/cc]
However, sometimes this isn't even enough. Let's say, for example, we want to break things up into 10 minute intervals. Get can further nest DATEADD()s and DATEDIFF()s. The below example is for the floor of every 10 minutes, but it is extendable to other date-types (days, hours, months, etc) and other time-intervals (10, 20, 3, etc).
[cc lang="tsql"]
select DATEADD(mi,DATEDIFF(mi,0,dateadd(mi,datediff(mi,0,getdate())/10*10,0)),0)
[/cc]
What's happening in the above code is, from the inside out, we

1. Get today’s full datetime
2. Get the difference between step 1 and zero (in minutes), which is an integer.
3. We divide step 2 by 10 and then multiply that by 10, effectively flooring the value to the nearest number evenly divisible by 10.
4. We add the integer from step 3 to the start of eternity (dateadd’ing to zero), and we’ve got the floor of the current 10 minute interval.
5. After step 4, we have a datetime value, and we’re left with something like SELECT DATEADD(dd, DATEDIFF(dd, 0, <step 4 datetime>),0).

So we’ve got a method to get the floor of a time period, a function that abstracts the mess into something easily used, and then a method to get the floor of a time-increment. I’ll leave it up to the ambitious reader to get the last part, the 10 minute example, into a function as well.