December 7, 2001 at 12:16 pm
Can anybody provide assistance with the follwoing code. I am trying to update a table and I can not get this to work.
Update table1
set col1= cast when len(col1) = 5 then '0' + substring(col1, 1,1) + '/' + substring(col1, 2,2) + '/' + substring(col1, 4,2)
when len(col1)=6 then substring(deliv_date, 1,2) + '/' + substring(deliv_date, 3,2) + '/' + substring(deliv_date, 5,2)
End
-JG
December 7, 2001 at 1:16 pm
Do you mean case?
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 7, 2001 at 3:34 pm
December 8, 2001 at 8:18 pm
also do you really want to use different fields depending on the length. Looks like you may be trying to cope with a dropped leading 0 (and trying not to disclose that yuo are dealing with deliv_date).
This looks like a date.
You look like you have
ddmmyy with a possible dmmyy
so
right('0'+ fld,6) will give ddmmyy - which you could use insead of the case statement.
and a couple of stuff statements with the above should suffice.
Cursors never.
DTS - only when needed and never to control.
December 10, 2001 at 8:15 am
The right function works great, but I need to do a dateadd function to the col too. In order to do the dateadd I understand I must first put it into a date format i.e. 10/01/2001. The data I am pulling from is inconsistent in lengtht, It could be 5 or 6. Is there a way I can get by with a one line format?
-JG
December 10, 2001 at 8:44 am
I have the solution. I combined the Stuff, Right finction to get the proper format.
convert(varchar(10), dateadd(dd, term, stuff(stuff(right('0' + sdate, 6), 3, 0, '/'), 6, 0, '/')),101)
Thanks for all the assistiance! The right, and stuff function really helped!
-JG
December 10, 2001 at 9:50 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply