woensdag 27 april 2011

Reset hh:mm:sss to 00:00:000 for a given datetime column

Today for testing a conversion of a system I needed to reset a datetime column to get rid of the hh:mm:sss notation.

The new system did register a time with the date like 1999-05-07 10:45:000 while the old system only registerd the date part like 1999-05-07 00:00:000

To check te conversion I only needed to know or the dates where correct meaning I had to get rid of the time notation.

First found option i tried was Cast(Convert(varchar, GetDate(),105) as datetime)
this resulted in the well known error: cannot convert varchar to datetime because it's no valid date...

I then tried Cast(Convert(varchar, GetDate(),101) as datetime) this works but is it the correct way? I think best things to do with datetime is using datetime functions so my final option is:

DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

Geen opmerkingen: