Cannot transform mm/dd/yyyy in excel to csv yyyymm

Question

I am asked to export data from excel to flat file using ssis package. I have columns in excel file that is in mm/dd/yyyy date format but I want to convert it into yyyymmdd in the flat file. I have tried all measures that I know of but I am not to do it.

Can someone help me ?

Thanks

Answer 1

Select the cells in question and format them:

Custom > YYYYMMDD

and then SaveAs .csv

Answer 2

Use derived column transformation and tweak below expression as per your requirement.

(DT_WSTR,4)YEAR(Date) + “-” + RIGHT(“0″ + (DT_WSTR,2)MONTH(Date),2) + “-” + RIGHT(“0″ + (DT_WSTR,2)DAY(Date),2)

To know more about how to use derived column, follow this article.

Converting Strings to Dates in the Derived Column Component

More about Expressions

if your input columns is string, you can use this expression:

((DT_WSTR,4)Year(((DT_DATE)InputColumn)))+
RIGHT("0"+((DT_WSTR,2)Month(((DT_DATE)InputColumn))),2)+
RIGHT("0"+((DT_WSTR,2)Day(((DT_DATE)InputColumn))),2)

but if your input column is date you can use this one:

((DT_WSTR,4)Year(InputColumn))+
RIGHT("0"+((DT_WSTR,2)Month(InputColumn)),2)+
RIGHT("0"+((DT_WSTR,2)Day(InputColumn)),2)