x

Date Problem in SQl Server 2005

I have a field with data like DEC - 010509 (mmddyy) I am using substring to break it and get the date part out I am using this query to get the date out: CONVERT(datetime, SUBSTRING(CONVERT(VARCHAR(10), SUBSTRING(SSINumber_, 5, 6), 101), 1, 2) + '/' + SUBSTRING(CONVERT(VARCHAR(10), SUBSTRING(SSINumber_, 5, 6), 101), 3, 2) + '/' + '20' + SUBSTRING(CONVERT(VARCHAR(10), SUBSTRING(SSINumber_, 5, 6), 101), 5, 2), 101)

I am getting an error when ever i try to query the field "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."

more ▼

asked Jul 06, 2010 at 06:04 PM in Default

avatar image

TechAbhi
1 1 1 1

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

2 answers: sort voted first

Abhi...

The down/dirty method of getting this quickly...

DECLARE @myStringDate VARCHAR(12) SET @myStringDate = 'DEC - 010509' DECLARE @myConvertedDate DATETIME

SET @myConvertedDate = CONVERT(DATETIME, SUBSTRING(@myStringDate,CHARINDEX('-',@myStringDate,0)+2,6),101)
SELECT @myConvertedDate

Returns

----------------------- 2001-05-09 00:00:00.000

(1 row(s) affected)

This assumes that the column you are converting always contains data in the same format.

Regards,

Jay

more ▼

answered Jul 06, 2010 at 08:38 PM

avatar image

Jay D
128 2 3 6

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

if the mmddyy date is always at the last 6 characters

select  convert(datetime, right(yourcolumn, 6), 103)            
more ▼

answered Jul 06, 2010 at 11:20 PM

avatar image

Squirrel 1
1.6k 1 3 5

(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.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x2016
x80

asked: Jul 06, 2010 at 06:04 PM

Seen: 810 times

Last Updated: Jul 06, 2010 at 06:04 PM

Copyright 2016 Redgate Software. Privacy Policy