Change Date format from yyyy-mm-dd to mm/dd/yyyy Derived Column

Question

I have a .txt flat file source. I am importing that into ssis to do some validation, manipulation to generate a .csv file. Thus: the date comes in as string and needs to go out as string, the output is not for a database, but a csv that is loaded by a data import tool.

I am in need to convert the date format which is in yyyy-mm-dd to mm/dd/yyyy for certain date column. For example: DOB column has date 1984-03-16 needs to be converted to 03/16/1984. I am using derived column transformation but not able to figure it out.

Answer 1

Say your Date Column is called DateCol

You will need to extract Date parts from the column using string function and then you can use those date parts to form a date in the format you need.

Expressions to Extract Date Parts

derivedColumns   Expression 
year             SUBSTRING(DateCol, 1, 4)
day              RIGHT(DateCol, 2)
Month            SUBSTRING(DateCol, 6, 2)

The Expression for the derived column:

NewDateColumn AS   SUBSTRING(DateCol, 6, 2) 
                 + "/" 
                 + RIGHT(DateCol, 2)
                 + "/"
                 + SUBSTRING(DateCol, 1, 4)

Your NewDate Column will be in your desired format