July 30, 2011 at 2:52 am
I have a column WEEK which is VARCHAR and has values like 201112,201135 in which last two characters represend the week no...so I need to create a derived column NEW_WEEK in such a way that
if (cast( SUBSTRING( WEEK,5,2) as int)>=1 AND cast( SUBSTRING( WEEK,5,2) as int)<=17)
Then NEW_WEEK=( cast( SUBSTRING( WEEK,5,2) as int)+17)
ELSE (cast( SUBSTRING( WEEK,5,2) as int)-17)
I am using following expression..
(((DT_I4) SUBSTRING( WEEK,5,2)>=1) && ((DT_I4) SUBSTRING( WEEK,5,2)<=17))?((DT_I4) SUBSTRING( WEEK,5,2)+35):((DT_I4) SUBSTRING( WEEK,5,2)-17))
Can somebody tell me whats wrong in it?
I also tried..
IIF((DT_I4) [SUBSTRING( CALWEEK,5,2)]>=1 && (DT_I4) [SUBSTRING( CALWEEK,5,2)]<=17) then ((DT_I4) [SUBSTRING( CALWEEK,5,2)]+35) else ((DT_I4) [SUBSTRING( CALWEEK,5,2)]-17))
its also not working 🙁
July 30, 2011 at 3:01 am
What do you mean by 'not working'? Wrong result, or error? If error, please post the error.
Have you considered doing this in T-SQL as part of your select?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 30, 2011 at 10:56 am
Following expression worked at last..
(DT_I4) SUBSTRING( WEEK,5,2) >=1 && (DT_I4) SUBSTRING( WEEK,5,2) <= 17 ? (DT_I4) (SUBSTRING( WEEK,5,2))+ 35 : (DT_I4) (SUBSTRING( WEEK,5,2))-17
Thanks Deepak
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply