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)