July 18, 2018 at 11:35 am
Trying to create an SSIS Expression in the Derived Column Transformation Editor that will add a leading zero to day and month of text values like this
3/3/2015 = 03/03/2015
only add zero to day value of these
12/4/2015 = 12/04/2015
only add zero to month value of these
1/11/2015 = 01/11/2015
testing with Excel I created this
=IF(ISBLANK(F19),"",IF(FIND("/",F19,1)=2,IF(FIND("/",F19,3)=4,REPLACE(REPLACE(F19,1,0,0),4,0,0),REPLACE(F19,1,0,0)),IF(FIND("/",F19,4)=5,REPLACE(F19,4,0,0),F19)))
So I thought I would be able to adapt that to an SSIS Expression replacing FIND with FINDSTRING and Changing the syntax for REPLACE, but Expression syntax for REPLACE does not use a location and I'm running out of time...
This was my attempt at converting that to an Expression -- IT FAILS --
(DT_WSTR,50)(LEN(TRIM(NOTE_DT)) == 0 ? NULL(DT_WSTR,1) : TRIM(FINDSTRING(NOTE_DT,"/",1)==2 ? FINDSTRING(NOTE_DT,"/",3)==4 ? FINDSTRING(NOTE_DT,"/",3)==4 : REPLACE(NOTE_DT,1,0,0)) : FINDSTRING(NOTE_DT,"/",4)==5 ? REPLACE(NOTE_DT,4,0,0) : NOTE_DT ))
Can someone please help.
Thank you ..
Also I thought I had learned a secret way to use an expression editor in that "Derived Column Transform Editor" (years ago) but I'll be darned I've forgotten.... Do you know how to do that?
July 18, 2018 at 11:51 am
Something like this:
right("0" + TOKEN( @[User::TextDate] , "/",1 ),2) + "/" + right("0"+TOKEN( @[User::TextDate] , "/",2 ),2) + "/" + TOKEN( @[User::TextDate] , "/",3 )
Replace @[User::TextDate] with your date variable.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 18, 2018 at 3:09 pm
Phil Parkin - Wednesday, July 18, 2018 11:51 AMSomething like this:
right("0" + TOKEN( @[User::TextDate] , "/",1 ),2) + "/" + right("0"+TOKEN( @[User::TextDate] , "/",2 ),2) + "/" + TOKEN( @[User::TextDate] , "/",3 )Replace @[User::TextDate] with your date variable.
AWESOME !! THANK YOU Phil !!
At first, I thought I was going to need to build conditional statements around it, BUT NO... 🙂
That worked perfectly, and I've added it to my tips list.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply