[sql-server] 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

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.

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!

you could just do this (SQL 2008):

declare @SomeDate date = getdate()

select @SomeDate

2009-05-28

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

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.

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

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

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

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.

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

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)

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)

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.