| Author |
 Topic  |
|
|
lmayer4
Starting Member
USA
11 Posts |
 Posted - 06/19/2012 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 06/19/2012 : 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
India
48102 Posts |
Posted - 06/19/2012 : 15:15:58
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
lmayer4
Starting Member
USA
11 Posts |
Posted - 06/20/2012 : 08:48:10
|
| Tragic indeed. Thanks so much for the help Jim! |
|
|
|
lmayer4
Starting Member
USA
11 Posts |
Posted - 06/20/2012 : 08:52:06
|
| Thanks also Visakh much appreciated. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 06/20/2012 : 08:55:07
|
You need to also set the dateformat option
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
|
|
convert mm/yy to datetime
