Integrationpublic

Integration

Get Involved. Join the Conversation.

This is a public Forum  public

Topic

    Yves Chassein
    Transform Date and Time to specific time zoneAnswered
    Topic posted June 20, 2018 by Yves ChasseinRed Ribbon: 250+ Points, last edited October 22, 2018 by Arijit ChakrabortyBronze Trophy: 5,000+ Points, tagged Eloqua, Integration 
    1248 Views, 10 Comments
    Title:
    Transform Date and Time to specific time zone
    Content:

    Hey,

    I'm facing the following challange: I have a CRM and a marketing system integrated by ICS. There is a field in the CRM in CET timezone but the marketing system need it in eastern time zone. Is there a easy way to transform from CET timezone to eastern time zone?

    Regards

    Yves

    Best Comment

    Yves Chassein

    Hey, thanks for the fast replys. I tried the following in the mapper: <xsl:value-of select = "string(fn:adjust-dateTime-to-timezone(xsd:dateTime('2018-01-01T10:00:00'), xsd:dayTimeDuration('PT5H')))">

     

    The result is 2018-01-01T10:00:00+05:00, which is not bad, but not exactly what I need. I need to push date and time back to Eloqua, and Eloqua can not work with timezone additions. So what I need as result is 2018-01-01T15:00:00. Is this somehow possible?

    Comment

     

    • Hemanth Lakkaraju

      Did you try using fn:adjust-dateTime-to-timezone () in mapper.

    • Jorge Herreria

      Depending on the data type there is a function to convert into a time zone

      • If CRM field is xs:time use fn:adjust-time-to-timezone(). Example: http://www.xqueryfunctions.com/xq/fn_adjust-time-to-timezone.html
      • For xs:dateTime use fn:adjust-dateTime-to-timezone
      • For xs:date use fn:adjust-date-to-timezone

      These fn:adjust-xxxx are available in Mapper and Assignments.

    • Ankur Jain

      Dear Yves,

      I have written a dedicated blog on how to change date and time from one timezone to another in ICS.

       

      https://www.techsupper.com/2017/11/convert-utc-date-time-to-another.html?m=1

       

      I'm sure that you want this exactly.

       

      Regards,

      Ankur

      https://www.techsupper.com

       

    • Yves Chassein

      Hey, thanks for the fast replys. I tried the following in the mapper: <xsl:value-of select = "string(fn:adjust-dateTime-to-timezone(xsd:dateTime('2018-01-01T10:00:00'), xsd:dayTimeDuration('PT5H')))">

       

      The result is 2018-01-01T10:00:00+05:00, which is not bad, but not exactly what I need. I need to push date and time back to Eloqua, and Eloqua can not work with timezone additions. So what I need as result is 2018-01-01T15:00:00. Is this somehow possible?

      • Ankur Jain

        Hi Yves,

        Please use substring-before function to get the value before + . Then you will get exactly you want.

        Regards,

        Ankur

         

         

    • Jorge Herreria

      If the datatype of Eloqua field/element is xs:dateTime, then you do not need the string() function. fn:adjust-dateTime-to-timezone(xsd:dateTime('2018-01-01T10:00:00'), xsd:dayTimeDuration('PT5H')) should do it.

      If Eloqua element is xsd:string (ugh!) then  I can think two ideas (I have not prove them, so do not expect them to be proven) :

      1. Try xsd:dateTime(fn:adjust-dateTime-to-timezone(xsd:dateTime('2018-01-01T10:00:00'), xsd:dayTimeDuration('PT5H')) )

      2. use combination fo substring-before and fn:timezone-from-dateTime(dateTime) to get the substring w/o the time zone.

       

      • Yves Chassein

        Hey Jorge,

        I tried the first apporach:

        <xsl:value-of select = "xsd:dateTime(fn:adjust-dateTime-to-timezone(xsd:dateTime('2018-01-01T10:00:00'), xsd:dayTimeDuration('PT5H')))">

        The result is: 2018-01-01T10:00:00+05:00

        I did not get the idea behind your second approach. 

         

        Regards

        Yves

    • Kevin

      I was able to just 'add'...

      <xsl:value-of select = "xsd:dateTime(/nssrcmpr:schedule/nssrcmpr:startTime) + xsd:dayTimeDuration('PT5H')">

      When I ran a test using:

      <ns0:startTime>2018-07-06T18:00:00.000</ns0:startTime>

      My result was:

      <nstrgmpr:messageId>2018-07-06T23:00:00</nstrgmpr:messageId>

       

      Does that work for you?

    • VR

      Hi, just wanted to know if you encountered any issues with EDT/EST time change. I have similar requirement. currently with EST it is set to -PT5H. But when EDT starts, how to handle the change?

     

    community forum