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
CONVERT, FLOOR ,and DATEDIFF will perform just the same.
Three methods described in the link below. I haven't performance tested them to determine which is quickest.
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)
CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
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