Date Problem in SQl Server 2005

Question

question

TechAbhi asked Jul 6, '10

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

sql-server-2005 datetime

10 |1200

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

Answer 1 · 2010-07-06T20:38:56Z

Jay D answered Jul 6, '10

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

10 |1200

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

Answer 2 · 2010-07-06T23:20:25Z

Squirrel 1 answered Jul 6, '10

if the mmddyy date is always at the last 6 characters

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

10 |1200

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

question