| Author |
Topic |
|
rockstar283
Yak Posting Veteran
96 Posts |
 Posted - 2011-07-30 : 04:53:00
|
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 :( |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-07-30 : 05:35:10
|
(DT_I4)(SUBSTRING( WEEK,5,2)>=1 && SUBSTRING( WEEK,5,2)<=17) ? SUBSTRING( WEEK,5,2)+35) :SUBSTRING( WEEK,5,2)-17)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
rockstar283
Yak Posting Veteran
96 Posts |
Posted - 2011-07-30 : 12:57:03
|
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 |
|
|
|
|
|
|
SSIS Derived column IF..ELSE condition