Convert time in date as 00:00:00

I have a date column in below varchar format. How to convert it to datetime format and replace the time as 00:00:00?

Dateformats (varchar)
2011-08-01 00:00:00
2000-11-16 07:39:44
2020-06-06 07:51:42.644
2020-05-26 06:55:38.08

Expected result

Dateformats (datetime)
2011-08-01 00:00:00.000
2000-11-16 00:00:00.000
2020-06-06 00:00:00.000
2020-05-26 00:00:00.000

Answer

Just cast to a date:

cast(left(datecol, 10) as date)

You don’t actually need the left(), so this works:

cast(datecol as date)

But I like the left() to be explicit about the conversion.

If you actually want the time component, cast back:

cast(cast(left(datecol, 10) as date) as datetime)

Or, if you want the value as a string:

concat(left(datecol, 10), ' 00:00:00.000')

Note: You shouldn’t be storing date/time values as strings. You should be using the built-in types.