Post reply

Expression to convert (date) to (int) type & remove the slash: 02/02/2008 to 20080202

  • March 28, 2008 at 6:19 am

    #183917

    Hi, I need help please.

    I have text source & i need to write it to table field being an integer.

    I complains about the column, so i added a derived column but I am clueless in the expression:

    Source from text: 02/02/2008

    Derived column expression to output: 20080202 in a int datatype

    I need help with the expression:

    (DT_UI4)((DT_STR,4,1252)YEAR([Trx_Date]) + (DT_STR,2,1252)MONTH([Trx_Date]) + (DT_STR,2,1252)DAY([Trx_Date]))

    Error: The function Year does not support the data type DT_UI4 for parameter 1. Has errors such as divide by zeros.

    Please Assist!

    Regards

  • March 31, 2008 at 4:39 am

    #795840

    Hi, got it going!

    My expression did not work becuase the year/month/day only works if the datatype is datetime.

    So i done a DataConversion to: database timestamp

    and then derived column: (DT_STR,4,1252)YEAR([Copy of Trx_Date]) + RIGHT("00" + (DT_STR,2,1252)MONTH([Copy of Trx_Date]),2) + RIGHT("00" + (DT_STR,2,1252)DAY([Copy of Trx_Date]),2)

    Thank You ALL

    Regards

  • April 1, 2008 at 7:50 am

    #796490

    Find this more readible?

    Declare @i as int

    Set @i = cast(right('02/04/2008',4) + substring('02/04/2008',4,2) + left('02/04/2008',2) as int)

    select @i

    (I'm using '02/04/2008' as you can see the month and day)

  • April 1, 2008 at 8:00 am

    #796501

    Thank you - i'll will keep it for future references.

  • April 2, 2008 at 8:15 pm

    #797582

    Just curious. .

    In your target, are you storing date in a numeric field?

    What was the reasoning behind this decision? Why not store in in a datetime field?

    Was it done for efficiency?

Viewing 5 posts - 1 through 4 (of 4 total)

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