question

Tim 3 avatar image
Tim 3 asked

conversion of char data type to a datetime data type resulted in out-of-range

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?

sql-server-2005datetimedata-conversioncast-convert
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

Have you tried CONVERT?

DECLARE @test [varchar] (20)
SET @test = '2010-06-11'
SELECT CONVERT([datetime], @test)

Or CAST?

DECLARE @test [varchar] (20)
SET @test = '2010-06-11'
SELECT CAST (@test AS [datetime])
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scott Abrants avatar image
Scott Abrants answered

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.

2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scott!!! Excellent. I hope everyone felt that. The IQ on the site just went up a couple of points.
0 Likes 0 ·
Grant you are too kind! Looking at all of your answers they are very valuable and worthy of up-votes! Keep up the good work - you are an asset to the community! Thanks again
0 Likes 0 ·
Henrik Staun Poulsen avatar image
Henrik Staun Poulsen answered

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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.