Post reply

float to date SSIS

  • May 31, 2017 at 4:28 am

    #338012

    Hi
    new to SSIS

    in SQL a view I have successfully converted a float date "OrderYearMonth" that looks like 201601 to a date

    note: just given a random day of 01

    so it appears 2016/01/01, 2016/02/01  ect

    however i am trying to do the same in SSIS, by using a derived column transformation

    CONVERT( DATE, CONVERT(VARCHAR(8), CONVERT(INT, OrderYearMonth* 100 + 1)))

    It does not seem to work

    please help

  • May 31, 2017 at 5:36 am

    #1944693

    joanna.seldon - Wednesday, May 31, 2017 4:27 AM

    Hi
    new to SSIS

    in SQL a view I have successfully converted a float date "OrderYearMonth" that looks like 201601 to a date

    note: just given a random day of 01

    so it appears 2016/01/01, 2016/02/01  ect

    however i am trying to do the same in SSIS, by using a derived column transformation

    CONVERT( DATE, CONVERT(VARCHAR(8), CONVERT(INT, OrderYearMonth* 100 + 1)))

    It does not seem to work

    please help

    Simpler approach
    😎

    CONVERT(DATE,(CONVERT(VARCHAR(8),OrderYearMonth,0) + '01'),112)

  • May 31, 2017 at 5:39 am

    #1944694

    The problem is that you're coding in T-SQL and SSIS uses a different language for derived columns.
    Here's a formula you can use:

    (DT_DBTIMESTAMP)((DT_WSTR,4)(OrderYearMonth / 100) + "-" + (DT_WSTR,2)(OrderYearMonth % 100) + "-01")

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • May 31, 2017 at 5:44 am

    #1944695

    Luis Cazares - Wednesday, May 31, 2017 5:39 AM

    The problem is that you're coding in T-SQL and SSIS uses a different language for derived columns.

    He he, good point Luis, I missed the obvious
    😎

  • May 31, 2017 at 6:54 am

    #1944703

    hi

    I am receiving an error with the code

    cannot use DT_DBTIMESTAMP with a binary operator "/"

    I have tried to change this to Database date [DT_DBDATE] with no joy

    I have also tried to put a data conversion, before the derived column transformation  , with no joy

    I don't need the time , just the date, but time date would be good if easier

    please help

  • May 31, 2017 at 7:47 am

    #1944717

    joanna.seldon - Wednesday, May 31, 2017 6:54 AM

    hi

    I am receiving an error with the code

    cannot use DT_DBTIMESTAMP with a binary operator "/"

    I have tried to change this to Database date [DT_DBDATE] with no joy

    I have also tried to put a data conversion, before the derived column transformation  , with no joy

    I don't need the time , just the date, but time date would be good if easier

    please help

    Please post the formula that you're using. The one that I posted is tested and worked fine.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • May 31, 2017 at 7:54 am

    #1944719

    Hi

    the source is an excel document

    OrderYearMonth
    201601
    201501
    201401

    I am using then a data conversion for the column OrderYearMonth     - DT_DBTIMESTAMP

    the I am using the derived column transformation to place this code

    (DT_DBTIMESTAMP)((DT_WSTR,4)(OrderYearMonth / 100) + "-" + (DT_WSTR,2)(OrderYearMonth % 100) + "-01")

    with no joy - with error DT_DBTIMESTAMP cannot be used with a binary operator "/"

    please help

  • May 31, 2017 at 8:07 am

    #1944723

    joanna.seldon - Wednesday, May 31, 2017 7:54 AM

    Hi

    the source is an excel document

    OrderYearMonth
    201601
    201501
    201401

    I am using then a data conversion for the column OrderYearMonth     - DT_DBTIMESTAMP

    the I am using the derived column transformation to place this code

    (DT_DBTIMESTAMP)((DT_WSTR,4)(OrderYearMonth / 100) + "-" + (DT_WSTR,2)(OrderYearMonth % 100) + "-01")

    with no joy - with error DT_DBTIMESTAMP cannot be used with a binary operator "/"

    please help

    In the Advance Editor (right click on the source), Input and Output Properties tab. What data type is OrderYearMonth on the Output Columns?
    If it's a float, then you need to convert the column to int before any operation.

    (DT_DBTIMESTAMP)((DT_WSTR,4)((DT_I4)OrderYearMonth / 100) + "-" + (DT_WSTR,2)((DT_I4)OrderYearMonth % 100) + "-01")

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • May 31, 2017 at 8:54 am

    #1944743

    Hi

    the excel source column type is a float

    and changing the file type to the int worked!

    brill thanks team

Viewing 9 posts - 1 through 8 (of 8 total)

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