In SQL Server, how do I "floor" a DATETIME to the second/minute/hour/day/year?
Let's say that I have a date of 2008-09-17 12:56:53.430, then the output of flooring should be:
The key is to use DATEADD and DATEDIFF along with the appropriate SQL timespan enumeration.
declare @datetime datetime;
set @datetime = getdate();
select @datetime;
select dateadd(year,datediff(year,0,@datetime),0);
select dateadd(month,datediff(month,0,@datetime),0);
select dateadd(day,datediff(day,0,@datetime),0);
select dateadd(hour,datediff(hour,0,@datetime),0);
select dateadd(minute,datediff(minute,0,@datetime),0);
select dateadd(second,datediff(second,'2000-01-01',@datetime),'2000-01-01');
select dateadd(week,datediff(week,0,@datetime),-1); --Beginning of week is Sunday
select dateadd(week,datediff(week,0,@datetime),0); --Beginning of week is Monday
Note that when you are flooring by the second, you will often get an arithmetic overflow if you use 0. So pick a known value that is guaranteed to be lower than the datetime you are attempting to floor.
In SQL Server here's a little trick to do that:
SELECT CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS float)) AS DATETIME)
You cast the DateTime into a float, which represents the Date as the integer portion and the Time as the fraction of a day that's passed. Chop off that decimal portion, then cast that back to a DateTime, and you've got midnight at the beginning of that day.
This is probably more efficient than all the DATEADD and DATEDIFF stuff. It's certainly way easier to type.
Too bad it's not Oracle, or else you could use trunc() or to_char().
But I had similar issues with SQL Server and used the CONVERT() and DateDiff() methods, as referenced here
Since PostgreSQL is also a "SQL Server", I'll mention
date_trunc()
Which does exactly what you're asking gracefully.
For example:
select date_trunc('hour',current_timestamp);
date_trunc
------------------------
2009-02-18 07:00:00-08
(1 row)
