SSIS Derived Column to add first 3 values of current year (YYY) to a string date (MMDDY)

Question

I have a string date value that is missing the 201 of 2019. The string value 03189101545 which should be 03-18-(201)9 10:15:45

I did a left from a serial number variable to extract 03189 and can't quite figure out how to add back the full year.

LEFT([Serial Number],5)

I've tried a cast to date but that fails.

(DT_DBTIME)LEFT([Serial Number],5)

I though about doing a

(DT_DATE) DATEADD("yyyy", 0, (LEFT([Serial Number],5)))

but it seems to be the same problem as the cast issue.

score 1 · Accepted Answer · answered Feb 27 '20 at 11:32

Try using to following expression, it converts the string into ISO format yyyy-MM-dd HH:mm:ss then cast it as date:

(DT_DATE)("201" + SUBSTRING([Serial Number], 5,1) + "-" +
    LEFT([Serial Number], 2) + "-" +
    SUBSTRING([Serial Number], 3,2) + " " +
    SUBSTRING([Serial Number], 6,2) + ":" +
    SUBSTRING([Serial Number], 8,2) + ":" +
    RIGHT([Serial Number], 2))

If you are looking to only add 201 to the existing value, use:

LEFT([Serial Number], 4) + "201 + RIGHT([Serial Number], 7)

Awesome, thanks! I appreciate the two approaches to the issue. — Neil B., Feb 27 '20 at 14:45

SSIS Derived Column to add first 3 values of current year (YYY) to a string date (MMDDY)

1 Answers1