Floor a date in SQL server

Question

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:

• Year: 2008-01-01 00:00:00.000
• Month: 2008-09-01 00:00:00.000
• Day: 2008-09-17 00:00:00.000
• Hour: 2008-09-17 12:00:00.000
• Minute: 2008-09-17 12:56:00.000
• Second: 2008-09-17 12:56:53.000

And yes, I fully intend on answering my own question in about 30 seconds. I don't think there's any SO etiquette against that.

Answer 1

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');

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.

Answer 2

The CONVERT() function can do this as well, depending on what style you use.

Answer 3

Not only is there no SO etiquette against that, it is actually encouraged :)

Answer 4

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

Answer 5

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.

Answer 6

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)