ssis derived column format

Question

in ssis I'm loading a data file that has a date field formatted as 11171977 into a table with a data type of varchar(8).

i can load this into the table fine; however, I need to reformat the data as yyyymmdd so it is 19771117.

my first attempt was to load as is then query with the following convert:

convert(varchar(8),convert(date,substring (BirthDate,1,2)+'/'+substring (BirthDate,3,2)+'/'+substring (BirthDate,5,4),101), 112)

that gives me yyyymmdd = 19771117.

however, I tried to do a derived column with no luck as it didn't like my expression.

how do i do this in an expression in the derived column?

Sounds like your destination table data type is VARCHAR(8), Why go from string to date back to string when the string just needs a simple rearrangement? — Cade Roux
so if i use a derived column, how would i do a string rearrangement? — WMBC
Look for a substring() function! Have you already tried this? Show us what exactly you have tried. — Giorgos Altanis
right(BirthDate,4)+left(BirthDate,2)+substring(BirthDate,3,2) — WMBC
I think you could simplify it to right(BirthDate,4)+left(BirthDate,4). Do you get an error? — Giorgos Altanis

Giorgos Altanis Giorgos Altanis · Accepted Answer · 2017-02-23T21:05:48

2

votes

Try something like this:

right(BirthDate,4)+left(BirthDate,4)