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

TechAbhi gravatar 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

Jay D gravatar image

Jay D
128 1 1 3

(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

Squirrel 1 gravatar image

Squirrel 1
1.6k 1 3

(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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1945
x94

asked: Jul 06, 2010 at 06:04 PM

Seen: 675 times

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