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?

more ▼

asked Jun 11 '10 at 12:12 PM in Default

Tim 3 gravatar image

Tim 3
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Have you tried CONVERT?

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


DECLARE @test varchar SET @test = '2010-06-11' SELECT CAST (@test AS [datetime]) 
more ▼

answered Jun 11 '10 at 12:17 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jun 11 '10 at 02:41 PM

Scott Abrants gravatar image

Scott Abrants

Scott!!! Excellent. I hope everyone felt that. The IQ on the site just went up a couple of points.
Jun 14 '10 at 09:13 AM Grant Fritchey ♦♦
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
Jun 14 '10 at 10:04 AM Scott Abrants
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Jun 14 '10 at 09:30 AM

Henrik Staun Poulsen gravatar image

Henrik Staun Poulsen
579 13 14 16

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jun 14 '10 at 05:44 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
15.9k 15 19 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 11 '10 at 12:12 PM

Seen: 7978 times

Last Updated: Jun 11 '10 at 12:53 PM