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?
You probably have data in your table that is not a date. Then your conversion will not work, not matter which conversion method you chose.
Have you tried the IsDate() function?
SELECT Convert(varchar(19), dob, 121) from yourTable where isDate(dob)=1
When working with dates as input from text, I always convert to the format 'YYYYMMDD hh:mm:ss.nnn', because that's a format that will always work regardless of regional settings.
If you have dates stored as 'YYYY-MM-DD', then you should convert to this to be totally sure it's interpreted correctly: left(dob,4)+substring(dob,6,2)+substring(dob,9,2) + ' 00:00:00.000'
If the value is stored in a string-column (like char, or varchar), you don't have to add any quotes.
Please note also that if you store 'YYYYMMDD' in a char(10)-column, it's stored as 'YYYYMMDD ' (two blanks in the end). I haven't tested, but I suspect that might also lead to problems when using implicit conversion.
answered Jun 14, 2010 at 05:44 PM