Search code examples
sql-serverdatessisetldate-conversion

SSIS convert date in mddyyyy and mmddyyyy format to date format using SSIS expression

I'm receiving my dates in a MMddyyyy and a Mddyyyy format in one source, if the month has 1-9 digit the month is 1 digit, if the month is between 10-12 its 2 digits, I need an SSIS expression that will be able to convert them into a date format.

ex 2051994 or 12051994

both from the same source but they are hard to convert with the same SSIS expression

Solution

  • The cleanest way to do it would be to normalise the input format and then convert it to data as usual to the format of your choice. In order to normalise the input, i.e. make it the same length for every month, you can add a Derived Column transformation with this expression:

    RIGHT("0" + yourinputdate,8)

    if the input date is integer instead of string you can do this:

    RIGHT("0" + (DT_STR,8,1252)(yourinputdate),8)

    using the above expressions 12051994 will remain unchanged but 2051994 will be converted to 02051994.

    from this transformation downstream you can convert the string to the format you prefer, or you can do it all in the same transformation although it would make the expression a bit unreadable.