Retrieving a datetime with a time of midnight (from a typical datetime)

Nothing difficult, I just need a way to generate a new datetime column based on the column [PostedDate], datetime. So basically I want to truncate the time. Thanks a lot.

A frequent method used is to (1) convert it to varchar using CONVERT with the 101 flavor and then (2) re-convert it back to datetime. Here are some examples:

Code Snippet

select convert(datetime, convert(varchar, getdate(), 101))
as dateOnly
/*
dateOnly
2007-09-07 00:00:00.000
*/

select dateadd(day, datediff (day, 0, getdate()), 0)
as dateOnly
/*
dateOnly
2007-09-07 00:00:00.000
*/

select cast(floor(cast(getdate() as float)) as datetime)
as dateOnly
/*
dateOnly
2007-09-07 00:00:00.000
*/

|||

Another way:

Code Snippet

select dateadd(d, datediff(d,0,[PostedDate]),0)

|||I used the dateadd method both of you suggested and it worked perfectly. Thank you very much.