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

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

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.

Solution

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