without - sql remove minutes and seconds from datetime

Most efficient way in SQL Server to get date from date+time? (8)

CAST(FLOOR(CAST(yourdate AS DECIMAL(12, 5))) AS DATETIME) performs the best by far. you can see the proof & tests when getting the date without time in sql server

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.

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)

in SQL server 2012 use

select cast(getdate() as date)


select cast(floor(cast(@datetime as float)) as datetime)

Works because casting a datetime to float gives the number of days (including fractions of a day) since Jan 1, 1900. Flooring it removes the fractional days and leaves the number of whole days, which can then be cast back to a datetime.

select cast(getdate()as varchar(11))as datetime