How can I truncate a datetime in SQL Server?

Asked 2009-05-28T21:28:03.747

329

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

sql sql-server t-sql datetime truncate

Edit Report

3 Answers

45

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)

answered 2009-05-28T22:15:46.160

1

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

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
```

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

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
```

answered 2009-06-25T04:14:47.053

-1

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

answered 2011-11-27T12:34:28.177

Your Answer