October 3, 2017 at 9:44 am
Dear all,
I am receiving from a excel file a string with the format (MM/DD/YYYY). I would like to transform it into (DD/MM/YYYY).
Can I do this with a data conversion task or do I need to create a script task?
Thank you
October 3, 2017 at 10:15 am
What is the data type of the column set to? Is it text, or date with the format MM/dd/yyyy? If the latter, then the date is simply stored as a integer and the formatting provides the date. For example if you were to enter the number 43011 into Excel and then format to "MM/dd/yyyy" you'd get "10/03/2017". If this is the case, then SSIS will treat the date as a date, and will display it in your language default. if you're inserting it into your SQL server, then it will also treat it as a date.
If the former, then you'll need to do some string manipluation. Using a variable with the value "10/03/2017" this returns a date for 2017-10-03. If you want it as a string, this should still put you on the right path:(DT_DATE) (RIGHT( @[User::DateVar],4) + "-" + LEFT( @[User::DateVar],2) + "-" + SUBSTRING( @[User::DateVar] , 4, 2))
Edit: ffs 😀 = : D (without the space)
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 3, 2017 at 10:16 am
I would use a derived column transform with an expression like:
(DT_STR,4,1252)YEAR([MyDateCol]) + RIGHT("0" + (DT_STR,2,1252)MONTH([MyDateCol]),2) + RIGHT("0" + (DT_STR,2,1252)DAY([MyDateCol]),2)
October 3, 2017 at 10:53 am
Hello, Thank you very much for your replies. This is str format column.
To put it working I had to create a devrivated column and put the below expression:
(DT_STR,12,1252)(RIGHT("0" + ((DT_STR,2,1252)MONTH(((DT_DATE)Trade_Date))),2) + "/" + RIGHT("0" + ((DT_STR,2,1252)DAY(((DT_DATE)Trade_Date))),2) + "/" + ((DT_STR,4,1252)YEAR(((DT_DATE)Trade_Date))))
Now, it works properly.
Thank you
October 7, 2017 at 2:24 pm
Where would you input that string derivation code? I tried to enter it into the Data type field in the SSIS wizard - Advanced section...it doesn't work there.
October 7, 2017 at 4:10 pm
mjdemaris - Saturday, October 7, 2017 2:24 PMWhere would you input that string derivation code? I tried to enter it into the Data type field in the SSIS wizard - Advanced section...it doesn't work there.
The expression would be placed in a Derived Column Transformation node.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 7, 2017 at 4:13 pm
Excuse my ignorance, but what is that and where do i find it?
October 8, 2017 at 6:28 am
mjdemaris - Saturday, October 7, 2017 4:13 PMExcuse my ignorance, but what is that and where do i find it?
In SSIS (in SSDT), it'll be in your SSIS toolbox when you're editting your Data Flow Task. Derived Columns Transformation are simply that, that are a transformation that is used to derive columns, which can either be new columns, or replace existing ones in your data flow.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply