Post reply

dateadd in the derived column transformation

  • February 10, 2016 at 3:16 am

    #311329

    Hi

    I have the following expression im using to create the date on the derived column:

    SUBSTRING(ReceivedOn,1,4) + "/" + SUBSTRING(ReceivedOn,5,2) + "/" + SUBSTRING(ReceivedOn,7,2) + " " + SUBSTRING(ReceivedOn,9,2) + ":" + SUBSTRING(ReceivedOn,11,2) + ":" + SUBSTRING(ReceivedOn,13,2)

    the output for the expression above is 2016/02/10 11:58 AM, I need to subtract 2 hours and the output should be 2016/02/10 09:58 AM. how do I do that.

    Thanks

  • February 10, 2016 at 6:18 am

    #1857683

    What is the format of the date (ReceivedOn) before you parse it?

  • February 10, 2016 at 11:33 pm

    #1857921

    Hi

    The date format is 2016/02/11 07:56:15, and I need to subtract 2hrs from the time, using the expression from my previous post.

  • February 11, 2016 at 2:37 am

    #1857937

    That doesn't make sense based on how you are parsing it. You are adding the slashes in yourself. If the output of your expression is 2016/02/11 09:56:15 then you should just be able to cast it and use DATEADD like so.

    DATEADD("Hh",-2,(DT_DBTIMESTAMP)(SUBSTRING(ReceivedOn,1,4) + "/" + SUBSTRING(ReceivedOn,5,2) + "/" + SUBSTRING(ReceivedOn,7,2) + " " + SUBSTRING(ReceivedOn,9,2) + ":" + SUBSTRING(ReceivedOn,11,2) + ":" + SUBSTRING(ReceivedOn,13,2)))

    If your output is 2016/02/10 11:58 AM, like you wrote in your first post, then it will not cast. If that is the case it would be worth looking at the input before you chop it up so we can see if that would convert easier.

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

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