Floor a date in SQL server
Solution 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');
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.
Solution 2
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.
Solution 3
Expanding upon the Convert/Cast solution, in Microsoft SQL Server 2008 you can do the following:
cast(cast(getdate() as date) as datetime)
Just replace getdate()
with any column which is a datetime.
There are no strings involved in this conversion.
This is ok for ad-hoc queries or updates, but for key joins or heavily used processing it may be better to handle the conversion within the processing or redefine the tables to have appropriate keys and data.
In 2005, you can use the messier floor: cast(floor(cast(getdate() as float)) as datetime)
I don't think that uses string conversion either, but I can't speak to comparing actual efficiency versus armchair estimates.
Solution 4
I've used @Portman's answer many times over the years as a reference when flooring dates and have moved its working into a function which you may find useful.
I make no claims to its performance and merely provide it as a tool for the user.
I ask that, if you do decide to upvote this answer, please also upvote @Portman's answer, as my code is a derivative of his.
IF OBJECT_ID('fn_FloorDate') IS NOT NULL DROP FUNCTION fn_FloorDate
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_FloorDate] (
@Date DATETIME = NULL,
@DatePart VARCHAR(6) = 'day'
)
RETURNS DATETIME
AS
BEGIN
IF (@Date IS NULL)
SET @Date = GETDATE();
RETURN
CASE
WHEN LOWER(@DatePart) = 'year' THEN DATEADD(YEAR, DATEDIFF(YEAR, 0, @Date), 0)
WHEN LOWER(@DatePart) = 'month' THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)
WHEN LOWER(@DatePart) = 'day' THEN DATEADD(DAY, DATEDIFF(DAY, 0, @Date), 0)
WHEN LOWER(@DatePart) = 'hour' THEN DATEADD(HOUR, DATEDIFF(HOUR, 0, @Date), 0)
WHEN LOWER(@DatePart) = 'minute' THEN DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @Date), 0)
WHEN LOWER(@DatePart) = 'second' THEN DATEADD(SECOND, DATEDIFF(SECOND, '2000-01-01', @Date), '2000-01-01')
ELSE DATEADD(DAY, DATEDIFF(DAY, 0, @Date), 0)
END;
END
Usage:
DECLARE @date DATETIME;
SET @date = '2008-09-17 12:56:53.430';
SELECT
@date AS [Now],--2008-09-17 12:56:53.430
dbo.fn_FloorDate(@date, 'year') AS [Year],--2008-01-01 00:00:00.000
dbo.fn_FloorDate(default, default) AS [NoParams],--2013-11-05 00:00:00.000
dbo.fn_FloorDate(@date, default) AS [ShouldBeDay],--2008-09-17 00:00:00.000
dbo.fn_FloorDate(@date, 'month') AS [Month],--2008-09-01 00:00:00.000
dbo.fn_FloorDate(@date, 'day') AS [Day],--2008-09-17 00:00:00.000
dbo.fn_FloorDate(@date, 'hour') AS [Hour],--2008-09-17 12:00:00.000
dbo.fn_FloorDate(@date, 'minute') AS [Minute],--2008-09-17 12:56:00.000
dbo.fn_FloorDate(@date, 'second') AS [Second];--2008-09-17 12:56:53.000
Solution 5
The CONVERT() function can do this as well, depending on what style you use.
Portman
Independent software engineer. A few recent projects: Shuffletime, "play the internet" in an online game Woofer, a "macroblogging" spoof on Twitter Squeaker, the "nanoblogging" spoof on Twitter Feeling Unlucky, the world's worst search engine -- Are you Google? My name is Portman Wills. Please add this page to your index.
Updated on July 05, 2022Comments
-
Portman almost 2 years
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
-
Portman almost 16 yearsActually, that's 25% more characters than dateadd(day,datediff(day,0,@datetime),0), so it's not easier to type. It's also 15% less efficient.
-
Portman almost 16 yearsWe've found that CONVERT() can be anywhere from 10% to 5x less performant than dateadd/datediff. SQL imposes a penalty for converting between numeric types and strings and then back again.
-
MatBailie over 14 yearsThe date you calculate your offset from doesn't need to be in the past. Any date will work, provided it is itself 'FLOOR'ed to the interval in questions. If the base date is in the future, you just get a negative offset value...
-
Hogan almost 13 years@Portman - is there a basis for your claim it is 15% less efficient?
-
Davin Studer almost 11 yearsTo floor to the week use this if Sunday is the first day of the week ... select dateadd(week,datediff(week,0,@datetime),-1)
-
Davin Studer almost 11 yearsUse this if Monday is the first day of the week ... select dateadd(week,datediff(week,0,@datetime),0)
-
Rik over 10 yearscasting to floor hurts performance since it will skip any datetime indexes. When using SQL 2008 it's better to use the datediff functions or CAST( [field] AS TIME) or CAST( [field] as DATE)
-
Hogan over 9 yearsA prior answer suggested this and was commented on (slower)
-
Adam about 8 yearsNot sure why this answer isn't at the top, this is a very efficient for date formatting in Microsoft SQL Server
-
droid over 4 yearsThis doesn't work in SQL Server 2008 R2: Explicit conversion from data type date to float is not allowed. stackoverflow.com/a/5505975/1919692
-
mahendra rajeshirke over 4 years@droid - I just tested it in SQL Server 2014, and it works fine.
-
N8allan over 3 yearsI suggest that you use case lower(@DatePart) when 'year'..., rather than case when lower(... to avoid all the unnecessary code and lower conversions.