Date Problem in SQl Server 2005

Question

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

Answer 1

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

Answer 2

0

if the mmddyy date is always at the last 6 characters

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

answered Jul 06 '10 at 11:20 PM

Squirrel 1
1.6k ● 1 ● 3

(comments are locked)

Date Problem in SQl Server 2005

Follow this question

Related Questions