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?
Answer by Matt Whitfield ·
Have you tried CONVERT?
DECLARE @test [varchar] (20) SET @test = '2010-06-11' SELECT CONVERT([datetime], @test)
DECLARE @test [varchar] (20) SET @test = '2010-06-11' SELECT CAST (@test AS [datetime])
Answer by Scott Abrants ·
When working with dates I always convert them to a known standard. For example most of the time I opt for converting them to something like this... SELECT CONVERT(VARCHAR(10),GETDATE(),110) will get you date with no time portion - useful for looking at betweens of whole dates for example. What is the interesting part is the third argument of the convert function that is called the style argument. Check BOL for more on this - it lists all of the styles that are supported and explains what to do when converting to datetime or when you convert to character data.
Cast and convert are your friends when dealing with dates and are only the tip of the iceberg.
Answer by Magnus Ahlkvist ·
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.
Answer by Henrik Staun Poulsen ·
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