Convert Date with Month abbreviation into date using SSIS Derived Column

Question

I'm receiving dates in a format like "04OCT1954" and want to convert it to a proper date to ingest into SQL Server using SSIS derived Column.

How would that be possible?

Thanks!

Answer 1

Use the below in the Derived Column Expression - replacing 'date'field' with your field

(DT_DBTIMESTAMP)(SUBSTRING(date_field,1,2) + "-" + SUBSTRING(date_field,3,3) + "-" + SUBSTRING(date_field,6,4))

It simply puts dashes between the year, month, day values and coverts it to a DT_DBTIMESTAMP format.

Answer 2

The date you're receiving is in "ddMMMyyyy" format, you can use a Derived Column Transformation and build an expression to convert your date to a compatible format. You can try the following expression :

SUBSTRING(OldDate,6,4) + "-" +
    (SUBSTRING(OldDate,3,3) == "JAN" ? "01" : 
    SUBSTRING(OldDate,3,3) == "FEB" ? "02" : 
    SUBSTRING(OldDate,3,3) == "MAR" ? "03" : 
    SUBSTRING(OldDate,3,3) == "APR" ? "04" : 
    SUBSTRING(OldDate,3,3) == "MAY" ? "05" : 
    SUBSTRING(OldDate,3,3) == "JUN" ? "06" : 
    SUBSTRING(OldDate,3,3) == "JUL" ? "07" : 
    SUBSTRING(OldDate,3,3) == "AUG" ? "08" : 
    SUBSTRING(OldDate,3,3) == "SEP" ? "09" : 
    SUBSTRING(OldDate,3,3) == "OCT" ? "10" : 
    SUBSTRING(OldDate,3,3) == "NOV" ? "11" : 
    "12") + "-" +
SUBSTRING(OldDate,1,2)

You will need first to extract the year, then determine the month based on the 3-letter code, and finally extract the day. I concatenated all three parts together, separated by hyphens.