How can I truncate a datetime in SQL Server?

Question

What's the best way to truncate a datetime value (as to remove hours minutes and seconds) in SQL Server?

For example:

declare @SomeDate datetime = '2009-05-28 16:30:22'
select trunc_date(@SomeDate)

-----------------------
2009-05-28 00:00:00.000

Cog

Answer 1

This continues to frequently gather additional votes, even several years later, and so I need to update it for modern versions of Sql Server. For Sql Server 2008 and later, it's simple:

cast(getdate() as Date)

Note that the last three paragraphs near the bottom still apply, and you often need to take a step back and find a way to avoid the cast in the first place.

But there are other ways to accomplish this, too. Here are the most common.

The correct way (new since Sql Server 2008)

cast(getdate() as Date)

The correct way (old)

dateadd(dd, datediff(dd, 0, getdate()), 0)

This is older now, but it's still worth knowing because it can also easily adapt for other time points, like the first moment of the month, minute, hour, or year.

This correct way uses documented functions that are part of the ANSI standard and are guaranteed to work, but it can be somewhat slower. It works by finding how many days there are from day 0 to the current day, and adding that many days back to day 0. It will work no matter how your datetime is stored and no matter what your locale is.

The fast way

cast(floor(cast(getdate() as float)) as datetime)

This works because datetime columns are stored as 8-byte binary values. Cast them to float, floor them to remove the fraction, and the time portion of the values are gone when you cast them back to datetime. It's all just bit shifting with no complicated logic and it's very fast.

Be aware this relies on an implementation detail that Microsoft is free to change at any time, even in an automatic service update. It's also not very portable. In practice, it's very unlikely this implementation will change any time soon, but it's still important to be aware of the danger if you choose to use it. And now that we have the option to cast as a date, it's rarely necessary.

The wrong way

cast(convert(char(11), getdate(), 113) as datetime)

The wrong way works by converting to a string, truncating the string, and converting back to a datetime. It's wrong, for two reasons:

1. It might not work across all locales and
2. It's about the slowest possible way to do this... and not just a little; it's like an order of magnitude or two slower than the other options.

---

Update This has been getting some votes lately, and so I want to add to it that since I posted this I've seen some pretty solid evidence that Sql Server will optimize away the performance difference between "correct" way and the "fast" way, meaning you should now favor the former.

In either case, you want to write your queries to avoid the need to do this in the first place. It's very rare that you should do this work on the database.

In most places, the database is already your bottleneck. It's generally the server that's the most expensive to add hardware to for performance improvements and the hardest one to get those additions right (you have to balance disks with memory, for example). It's also the hardest to scale outward, both technically and from a business standpoint; it's much easier technically to add a web or application server than a database server and even if that were false you don't pay $20,000+ per server license for IIS or Apache.

The point I'm trying to make is that whenever possible you should do this work at the application level. The only time you should ever find yourself truncating a datetime on Sql Server is when you need to group by the day, and even then you should probably have an extra column set up as a computed column, maintained at insert/update time, or maintained in application logic. Get this index-breaking, CPU-heavy work off your database.

Coverdale

Joel, your update to this post is useful. What's strange is that I found the DateDiff method to be faster, at least in SQL 2008. I'll try running my tests in SQL 2000 and will see if I can post an update. – Hedda

the "fast way" is still the fastest way for sql 2008 according to a benchmark I just ran – Yardarm

FYI: https://mcmap.net/q/64164/-best-approach-to-remove-time-part-of-datetime-in-sql-server/27535 and https://mcmap.net/q/65498/-most-efficient-way-in-sql-server-to-get-date-from-date-time/27535 The dateadd/datediff "wins...". For a single variable, who cares of course, and one hopes that you have computed columns or such over a million rows :-) – Agamic

This "correct" way only accidentally works. The way it is written is as if the syntax for DateAdd were (interval, date, increment), but it isn't. It is (interval, increment, date). I stumbled on this when I tried to truncate a date to the first of the month: SELECT DATEADD( m, 0, DATEDIFF( m, 0, GETDATE( ) ) ) does not work, but SELECT DATEADD( m, DATEDIFF( m, 0, GETDATE( ) ), 0 ) does. At least, this is what I see in 2008R2. – Realm

@Kelly in 2008R2, why not just cast(getdate() as date)? – Coverdale

I ran into substantial performance degradation when using the CAST method in SQL Server 2008 R2. I was using it in a query that covered ~8.5m rows, and it took at least half an hour (before I terminating the query). Removing the CAST method and using the "old correct way" made the query instant. – Direct

'The fast way' work on datetime column as well and not just on getdate() function. For example: This CAST(FLOOR(CAST(CreatedDate AS float)) AS datetime) AS CreatedDate work.., where CreatedDate is a column in your table. – Comity

They all work on a datetime column. getdate() here is a stand-in for whatever datetime source you may have. – Coverdale

Re: Get this cpu-heavy work off your database. Is it really THAT much work though? I mean my telephone can render a very complex 3D environment with millions of texture mapped triangles 60 times a second. Is removing the time from a date in SQL Server really going to cause a problem you could even measure (even for millions of rows)? – Sykes

Regarding my above comment: I just did some tests. All four methods take exactly the same amount of time to run to within 2%. I tested on a million iterations and it took 3300ms in each instance, to within 20ms. Even the "slow" way is just as fast as the other methods (SQL Server 2012). Removing the cast completely as a control saves about 30% (ie, SQL Server seems to spend 2 seconds in 1M iterations of an empty while loop - which surprised me!). – Sykes

Your telephone is only serving one user. Your DB needs to handle potentially thousands of users simultaneously, with each user running several queries and each query needing potentially thousands to millions of calculations. Yes, that CPU difference can definitely matter. For the testing... these things can change version to version, but you also need to be very sure you're running fair tests. Sql Server does a lot of things with caching, indexing, etc that can invalidate simple tests. – Coverdale

A couple of notes: 1) DateDiff/DateAdd from zero can also be used to at other time intervals besides day, DATEADD(MONTH, datediff(MONTH,0, GETDATE()), 0). 2) The fast way cast to float -> floor -> cast to datetime does not work for datetime2 unless additional casts to/from datetime are added. – Caligula

I like your solution "the correct way (old)" because it works in many many other usecases. – Aborn

Answer 2

For SQL Server 2008 only

CAST(@SomeDateTime AS Date) 

Then cast it back to datetime if you want

CAST(CAST(@SomeDateTime AS Date) As datetime)

Beaverette

Good point: I'm still on 2005 and so for 2008 this is probably the new "correct" way and may even match the performance of the "fast" way. – Coverdale

The performance of this new way is even faster than the "fast" way. – Hedda

Answer 3

Just for the sake of a more complete answer, here's a working way for truncating to any of the date parts down and including minutes (replace GETDATE() with the date to truncate).

This is different from the accepted answer in that you can use not only dd (days), but any of the date parts (see here):

dateadd(minute, datediff(minute, 0, GETDATE()), 0)

Note that in the expression above, the 0 is a constant date on the beginning of a year (1900-01-01). If you need to truncate to smaller parts, such as to seconds or milliseconds, you need to take a constant date which is closer to the date to be truncated to avoid an overflow.

Rosebay

This was monstrously helpful. I looked all over for a way to truncate the date-time at a place lower than the full day. – Maw

Just so the internet knows, you don't have to be restricted to full datepart periods. Here's an example for 15 minute intervals, using integer division: dateadd(minute, datediff(minute, 0, GETDATE()) / 15 * 15, 0) – Maw

Answer 4

The snippet I found on the web when I had to do this was:

 dateadd(dd,0, datediff(dd,0, YOURDATE))
 e.g.
 dateadd(dd,0, datediff(dd,0, getDate()))

Trounce

I'm on 2005, but I thought 2008 had some new function for this?? – Taught

Neat! I would have resorted to splitting out the dateparts and using string handling to putting them back together. May not be relevant, but SQL2008 has a pure date-only data type without a time element. – Zygodactyl

And note you have the DateAdd operands mixed up, it's DateAdd(dd, DateDiff(...), 0). This can bite you if you're not careful. – Hedda

Answer 5

CONVERT(DATE, <yourdatetime>) or CONVERT(DATE, GetDate()) or CONVERT(DATE, CURRENT_TIMESTAMP)

Earsplitting

Answer 6

When working with analytics you probably need date\time truncation a lot. So I made a small function to help sorting this out:

CREATE FUNCTION TRUNC_DATE
(
    @datetime datetime, -- datetime to be truncated
    @level VARCHAR(10)  -- truncation level: year, month, day, hour and minute
)
RETURNS DATETIME
AS
BEGIN

    IF (UPPER(@level) = 'YEAR')
       RETURN DATEADD(YEAR,   DATEDIFF(YEAR, 0, @datetime), 0)
    ELSE IF (UPPER(@level) = 'MONTH')
        RETURN DATEADD(MONTH,   DATEDIFF(MONTH, 0, @datetime), 0)
    ELSE IF(UPPER(@level) = 'DAY')
       RETURN DATEADD(DAY,   DATEDIFF(DAY, 0, @datetime), 0)
    ELSE IF (UPPER(@level) = 'HOUR')
       RETURN DATEADD(HOUR,   DATEDIFF(HOUR, 0, @datetime), 0)
    ELSE IF (UPPER(@level) = 'MINUTE')
       RETURN DATEADD(MINUTE,   DATEDIFF(MINUTE, 0, @datetime), 0)

    RETURN @datetime
END
GO

To evalute the function (change the GETDATE() with you column):

SELECT DBO.TRUNC_DATE(GETDATE(), 'YEAR')   YEAR;
SELECT DBO.TRUNC_DATE(GETDATE(), 'MONTH')  YEAR_MONTH;
SELECT DBO.TRUNC_DATE(GETDATE(), 'DAY')    YEAR_MONTH_DAY;
SELECT DBO.TRUNC_DATE(GETDATE(), 'HOUR')   YEAR_MONTH_DAY_HOUR;
SELECT DBO.TRUNC_DATE(GETDATE(), 'MINUTE') YEAR_MONTH_DAY_HOUR_MINUTE;

The output:

Parrott

Answer 7

In SQL 2005, your trunc_date function could be written like this.

1. The first method is much much cleaner. It uses only 3 method calls including the final CAST() and performs no string concatenation, which is an automatic plus. Furthermore, there are no huge type casts here. If you can imagine that Date/Time stamps can be represented, then converting from dates to numbers and back to dates is a fairly easy process.

   CREATE FUNCTION trunc_date(@date DATETIME)
   RETURNS DATETIME
   AS
   BEGIN
       CAST(FLOOR( CAST( @date AS FLOAT ) )AS DATETIME)
   END
   

2. If you are concerned about Microsoft's implementation of DATETIMEs, (2) or (3) might be okay.

   CREATE FUNCTION trunc_date(@date DATETIME)
   RETURNS DATETIME
   AS
   BEGIN
         SELECT CONVERT(varchar, @date,112)
   END
   

3. Third, the more verbose method. This requires breaking the date into its year, month, and day parts, putting them together in "yyyy/mm/dd" format, then casting that back to a date. This method involves 7 method calls including the final CAST(), not to mention string concatenation.

   CREATE FUNCTION trunc_date(@date DATETIME)
   RETURNS DATETIME
   AS
   BEGIN
   SELECT CAST((STR( YEAR( @date ) ) + '/' +STR( MONTH( @date ) ) + '/' +STR( DAY(@date ) )
   ) AS DATETIME
   END
   

Brushwood

Answer 8

SQL Server 2022 have DATETRUNC

DECLARE @d datetime2 = '2021-12-08 11:30:15.1234567';
SELECT 'Year', DATETRUNC(year, @d);
SELECT 'Quarter', DATETRUNC(quarter, @d);
SELECT 'Month', DATETRUNC(month, @d);
SELECT 'Week', DATETRUNC(week, @d); -- Using the default DATEFIRST setting value of 7 (U.S. English)
SELECT 'Iso_week', DATETRUNC(iso_week, @d);
SELECT 'DayOfYear', DATETRUNC(dayofyear, @d);
SELECT 'Day', DATETRUNC(day, @d);
SELECT 'Hour', DATETRUNC(hour, @d);
SELECT 'Minute', DATETRUNC(minute, @d);
SELECT 'Second', DATETRUNC(second, @d);
SELECT 'Millisecond', DATETRUNC(millisecond, @d);
SELECT 'Microsecond', DATETRUNC(microsecond, @d);

Year        2021-01-01 00:00:00.0000000
Quarter     2021-10-01 00:00:00.0000000
Month       2021-12-01 00:00:00.0000000
Week        2021-12-05 00:00:00.0000000
Iso_week    2021-12-06 00:00:00.0000000
DayOfYear   2021-12-08 00:00:00.0000000
Day         2021-12-08 00:00:00.0000000
Hour        2021-12-08 11:00:00.0000000
Minute      2021-12-08 11:30:00.0000000
Second      2021-12-08 11:30:15.0000000
Millisecond 2021-12-08 11:30:15.1230000
Microsecond 2021-12-08 11:30:15.1234560

https://learn.microsoft.com/en-us/sql/t-sql/functions/datetrunc-transact-sql?view=sql-server-ver16

Bran

The OP did specifically say 2008 in their request. – Humbuggery

@JeffModen Perhaps, but it's often necessary to update questions with newer functionality esp when older versions become deprecated. You wouldn't expect a question stating C# v2 to remain with only older answers. And this post is pretty much the canonical for this type of question – Otten

@Otten - Ah... ok. I see where you're coming from. Thanks for the explanation. – Humbuggery

Answer 9

For those of you who came here looking for a way to truncate a DATETIME field to something less than a whole day, for example every minute, you can use this:

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) + (FLOOR((CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT))) * 1440.0) + (3.0/86400000.0)) / 1440.0 AS DATETIME)

So if today was 2010-11-26 14:54:43.123 then this would return 2010-11-26 14:54:00.000.

To change the interval it truncates to, replace 1440.0 with the number of intervals in a day, for example:

Truncate to	Formula	Number
Hour	24 hours/day	24.0
Half hour	24 hours/day / 2	48.0
Minute	24 hours/day * 60 minutes/hour	1440.0

(Always put a .0 on the end to implicitly cast to a float.)

---

For those of you wondering what the (3.0/86400000) is for in my calculation, SQL Server 2005 doesn't seem to cast from FLOAT to DATETIME accurately, so this adds 3 milliseconds before flooring it.

Fashoda

Be careful with rounding errors due to floating point precision limits though... also, this doesn't work with the datetime2 data type. – Rosebay

For Hour, SELECT DATEADD( hour, DATEDIFF( hour, 0, GETDATE( ) ), 0 ) works, also. Minute, too, but Second will result in an overflow. – Realm

Casting to float and back to datetime doesn't work correctly. – Hedda

Answer 10

This query should give you result equivalent to trunc(sysdate) in Oracle.

SELECT  * 
FROM    your_table
WHERE   CONVERT(varchar(12), your_column_name, 101)
      = CONVERT(varchar(12), GETDATE(), 101)

Hope this helps!

Forename

Answer 11

You can also extract date using Substring from the datetime variable and casting back to datetime will ignore time part.

declare @SomeDate datetime = '2009-05-28 16:30:22'
SELECT cast(substring(convert(varchar(12),@SomeDate,111),0,12) as Datetime) 

Also, you can access parts of datetime variable and merge them to a construct truncated date, something like this:

SELECT cast(DATENAME(year, @Somedate) + '-' + 
       Convert(varchar(2),DATEPART(month, @Somedate)) + '-' +
       DATENAME(day, @Somedate) 
       as datetime)

Logway

Answer 12

Oracle:

TRUNC(SYSDATE, 'MONTH')

SQL Server:

DATEADD(DAY, - DATEPART(DAY, DateField) + 1, DateField)

Could be similarly used for truncating minutes or hours from a date.

Chemical

Answer 13

You could just use DATE with SQL 2008:

declare @SomeDate date = getdate()

select @SomeDate

Arbalest

Answer 14

select cast(floor(cast(getdate() as float)) as datetime) Reference this: http://microsoftmiles.blogspot.com/2006/11/remove-time-from-datetime-in-sql-server.html

Richert

Casting to float and back to datetime doesn't work correctly. – Hedda