convert mm/yy to datetime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

convert mm/yy to datetime

Author

Topic

lmayer4
Starting Member

33 Posts

Posted - 2012-06-19 : 14:35:22

Afternoon,

I am migrating data from our old system to our new system and I need to break out the lot field into 2 peices. One being the lot and one being the date.

Like so:
42793/EXP.06/12
45228/EXP.10/26/13

I wrote this:


SUBSTRING(lot,1,5)

which gives me 42793 and 45228 ...easy now it gets complicated at least for me. How do I take the "date" after the exp. and change it to a datetime? This works great on mm/dd/yy but not for mm/yy


CONVERT(datetime, substring(lot,charindex('exp',lot)+4,len(lot)))

I get the message :Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string. when I try to convert the mm/yy date.

Any thoughts or hints would be great.

Thanks

Laura

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-19 : 14:51:28

You'll need smoething tragic like

declare @table Table (col1 varchar(30))

INSERT INTO @table
VALUES ('42793/EXP.06/12') ,
('45228/EXP.10/26/13')

select SUBSTRING(Col1,1,5),convert(date,substring(Col1,11,2)+'/01/'+right(col1,2))
,LEN(SUBSTRING(Col1,11,20))

,CASE WHEN LEN(SUBSTRING(Col1,11,20)) = 5
THEN CONVERT(Date,substring(Col1,11,2)+'/01/'+right(col1,2))
ELSE CONVERT(date,substring(Col1,11,19))
END

from @table

but this comes with a lot warnings about what formats that field can take. This works on the samples you provided, but caveat emptor!

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-19 : 15:15:58

[code]
SELECT CONVERT(datetime,CASE WHEN LEN(Val) > 5 THEN Val ELSE STUFF(Val,4,0,'01/') END,101) AS DtVal
FROM
(
SELECT STUFF(Field,1,PATINDEX('%EXP.%',Field) + 4,'') AS Val FROM table
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lmayer4
Starting Member

33 Posts

Posted - 2012-06-20 : 08:48:10

Tragic indeed. Thanks so much for the help Jim!

lmayer4
Starting Member

33 Posts

Posted - 2012-06-20 : 08:52:06

Thanks also Visakh much appreciated.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-06-20 : 08:55:07

You need to also set the dateformat option

Madhivanan

Failing to plan is Planning to fail