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

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.

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)

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.

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()))

Answer 5

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

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:

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
   

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

Answer 9

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!

Answer 10

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)

Answer 11

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.

Answer 12

You could just use DATE with SQL 2008:

declare @SomeDate date = getdate()

select @SomeDate

Answer 13

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.

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