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

Transact-SQL

Jeff Roedel


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.

Top

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

Kent Waldrop Se07


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
*/





Top

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

rusag2

Another way:

Code Snippet

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






Top

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

Jeff Roedel

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




Top