How can I truncate a datetime in SQL Server?

问题:

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

For example:

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

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

回答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 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.

回答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)

回答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.

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

回答5:

In SQl 2005 your trunc_date function could be written like this.

(1)

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

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.

(2)

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

If you are concerned about microsoft\'s implementation of datetimes (2) or (3) might be ok.

(3)

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

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.

回答6:

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

回答7:

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

回答8:

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 trucates to, replace 1440.0 with the number of intervals in a day, for example:

24hrs          =   24.0  (for every hour)
24hrs / 0.5hrs =   48.0  (for every half hour)
24hrs / (1/60) = 1440.0  (for every minute)

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

回答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!

回答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)

回答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.

回答12:

you could just do this (SQL 2008):

declare @SomeDate date = getdate()

select @SomeDate

---

2009-05-28

回答13:

TRUNC(aDate, \'DD\') will truncate the min, sec and hrs

SRC: http://www.techonthenet.com/oracle/functions/trunc_date.php