I have a bunch of dates of birth (dob) in a table as char(10) like yyyy-mm-dd.
I've tried to convert them to datetime as they are and also by select left(dob,4)+substring(dob,6,2)+substring(dob,9,2)
In both cases I get this error: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
I've found that when data is formatted as 'yyyymmdd' or as 'dd/mm/yyyy' or as 'dd-mm-yyyy' the conversion works. My question is how to get the single quotes around the string. And why doesn't SQL see this as a string with single quotes anyway?