Most efficient way in SQL Server to get date from

Question

In MS SQL 2000 and 2005, given a datetime such as '2008-09-25 12:34:56' what is the most efficient way to get a datetime containing only '2008-09-25'?

Duplicated here.

Answer 1

CONVERT, FLOOR ,and DATEDIFF will perform just the same.

How to return the date part only from a SQL Server datetime datatype

Answer 2

in SQL server 2012 use

select cast(getdate() as date)

Answer 3

Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)

DateDiff(Day, 0, GetDate()) is the same as DateDiff(Day, '1900-01-01', GetDate())

Since DateDiff returns an integer, you will get the number of days that have elapsed since Jan 1, 1900. You then add that integer number of days to Jan 1, 1900. The net effect is removing the time component.

I should also mention that this method works for any date/time part (like year, quarter, month, day, hour, minute, and second).

Select  DateAdd(Year, DateDiff(Year, 0, GetDate()), 0)
Select  DateAdd(Quarter, DateDiff(Quarter, 0, GetDate()), 0)
Select  DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)
Select  DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
Select  DateAdd(Hour, DateDiff(Hour, 0, GetDate()), 0)
Select  DateAdd(Second, DateDiff(Second, '20000101', GetDate()), '20000101')

The last one, for seconds, requires special handling. If you use Jan 1, 1900 you will get an error.

Difference of two datetime columns caused overflow at runtime.

You can circumvent this error by using a different reference date (like Jan 1, 2000).

Answer 4

To get YYYY-MM-DD, use:

select convert(varchar(10), getdate(), 120)

Edit: Oops, he wants a DateTime instead of a string. The equivalent of TRUNC() in Oracle. You can take what I posted and cast back to a DateTime:

select convert(datetime, convert(varchar(10), getdate(), 120) , 120)

Answer 5

CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]