CODEWITHSUNDEEP
exceldatessis
apariyar
apariyar

Reputation: 35

Cannot transform mm/dd/yyyy in excel to csv yyyymmdd date format using SSIS

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

Upvotes: 1

Views: 4192

Answers (2)

Vikramsinh Shinde
Vikramsinh Shinde

Reputation: 2878

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)

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96781

Select the cells in question and format them:

Custom > YYYYMMDD

and then SaveAs .csv

Upvotes: 0

Related Questions