Convert value stored in a varchar to date time in sql

Question

I have a date stored in varchar variable I want to covert it to datetime format so that I could update the date to a column with type datetime

declare @dt_original as varchar(30)

set @dt_original original='1997-12-22 00:00:00.000'

I have tried

cast(@dt_original as date time)
Convert(datetime,@dt_original)

But nothing worked

Always throwing "conversion failed when converting date and/or time from character string"

If you want the variable to hold a date, why declare it as a varchar in the first place? — Larnu
It won't for non-Americans @forpas . For example, if your British: db<>fiddle. Despite what Microsoft thought (they've corrected it now for the new date and time data types) the rest of the world didn't read dates in the format yyyy-dd-MM. — Larnu

Mureinik Mureinik · Accepted Answer · 2021-05-16T13:08:21

The idea of using convert was correct, but you're missing the style pattern matching your string. In this case, it's 121:

CONVERT(DATETIME, @dt_original, 121)