Post reply

CONVERT and Concatenate NUMERIC to date in Derived Column

  • August 26, 2010 at 9:53 am

    #228366

    Hi all:

    I have data from DB2 that stores dates as numerics e.g. 20080430.

    I have three (3) separate columns EXPYear, EXPMonth, EXPDay all numeric. eg. 2010, 5, 26.

    How do I convert the numerics and concatenate the three columns into a date format in a derived column to output 05-26-2010

    Thanks

  • August 27, 2010 at 12:28 pm

    #1213180

    Try:

    (DT_DATE)EXPYear + "-" + EXPMonth + "-" + EXPDay

  • August 28, 2010 at 11:40 am

    #1213454

    shairalt (8/27/2010)

    Try:

    (DT_DATE)EXPYear + "-" + EXPMonth + "-" + EXPDay

    I think it is best that you include brackets, or otherwise SSIS might try to convert only EXPYear to a DT_DATE.

    I'm not sure about this (and I don' have SSIS on this computer to check), but it might be necessary to convert each integer first to a string, before you try to concatenate them.

    (DT_DATE)((DT_WST,4)EXPYear + "-" + (DT_WST,2)EXPMonth + "-" + (DT_WST,2)EXPDay)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply