question

Neha 1 avatar image
Neha 1 asked

convert datetime from varchar error

A table in my database has date stored in varchar(50) column.The date is '22/09/2009 13:15:13'.How do i convert it to datetime.I am getting an error when i try to select CONVERT(datetime,'22/09/2009 13:15:13'):

Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

datetimedatatypes
10 |1200

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

Use the third parameter to CONVERT, the style parameter.

For your particular style, you want to use style 103:

SELECT CONVERT(datetime,'22/09/2009 13:15:13', 103);

For more information, see the reference here: http://msdn.microsoft.com/en-us/library/ms187928.aspx

A good practice is always to use the ISO format of 'yyyymmdd' when using text dates, as that can't be mis-interpreted, and also sorts naturally in date order when using a text sort.

10 |1200

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

suraj avatar image
suraj answered

Try this

set dateformat dmy
select CONVERT(datetime,'22/09/2009 13:15:13')

Hope this will help

10 |1200

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

Fatherjack avatar image
Fatherjack answered

Its because the value is being read in the format mm/dd/yyyy and there isnt a 22nd month. If you change the format of the supplied date it should be fine

convert(datetime, '20090922 13:15:13') --uses the ISO standard format of yyyymmdd

convert(datetime, '2009-Sep-22 13:15:13') --uses the the month name to avoid confusion but may have mixed results if you have any access that uses a non english month name
convert(datetime, '09/22/2009 13:15:13') -- uses the format expected by your installation

hope this helps

7 comments
10 |1200

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

+1 - I shouldn't go for breakfast in the middle of answering posts... :)
1 Like 1 ·
Well I think that depends on your perspective... Certainly the eggs are nice, but I think they're pets really. It was the Gypsy Fair Day in the village I live in today, had a narrow escape as the wife almost purchased a horse...
1 Like 1 ·
breakfast?! Its nearly lunchtime. You Sir, are clearly not a horse owner. :D
0 Likes 0 ·
No, just the 4 chickens, 3 cats, 2 gerbils and 1 dog... (!)
0 Likes 0 ·
Hopefully well segregated to prevent it simply becoming the owner of 1 dog ... ?!
0 Likes 0 ·
Show more comments

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.