June 9, 2009 at 10:23 am
table contains date like
071009(mmddyy),42209 (mddyy)
how to convert this in datetime?
June 9, 2009 at 10:25 am
Is the day portion always 2 characters, in other words you don't have 1109 (mdyy)?
June 9, 2009 at 10:34 am
only
mmddyy or mddyy
day always 2 char
June 9, 2009 at 10:37 am
declare @temp as table (txtdate varchar(10))
insert into @temp select '071009' union select '42209'
select
CONVERT(datetime,
case
when LEN(txtdate) = 6 then STUFF(STUFF(txtdate, 3, 0, '-'), 6, 0, '-')
else '0' + STUFF(STUFF(txtdate, 2, 0, '-'), 5, 0, '-')
end, 10
)
from @temp
June 17, 2009 at 10:34 pm
Allister Reid (6/9/2009)
declare @temp as table (txtdate varchar(10))insert into @temp select '071009' union select '42209'
select
CONVERT(datetime,
case
when LEN(txtdate) = 6 then STUFF(STUFF(txtdate, 3, 0, '-'), 6, 0, '-')
else '0' + STUFF(STUFF(txtdate, 2, 0, '-'), 5, 0, '-')
end, 10
)
from @temp
You can remove your case statement by doing the following:
SELECT
CONVERT(datetime,
STUFF(STUFF(RIGHT('0'+txtdate,6),5,0,'-'), 3, 0, '-'),
10)
from @temp
/* Anything is possible but is it worth it? */
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply