Integration

Get Involved. Join the Conversation.

Topic

    Monosij Sinha
    Date Conversion errorAnswered
    Topic posted November 15, 2019 by Monosij SinhaGreen Ribbon: 100+ Points, last edited November 15, 2019, tagged Cloud, Integration, Mapping, PaaS 
    32 Views, 4 Comments
    Title:
    Date Conversion error
    Summary:
    Getting type/cast error when calculating Date 30 days before.
    Content:

    Hi - trying to get date 30 days before but getting error FORG0001: invalid value for cast/constructor. The Batch_Date is being read through a stage read activity and the csv schema already has the column type as date. Also tried to explicitly convert by putting it within xsd:date(). Still get the same error.

    Expresion used: <xsl:value-of select = "$currLineReadFile/nsmpr1:readFileRecord/nsmpr1:Batch_Date - xsd:dayTimeDuration('P30D')">

    Best Comment

    Hemanth Lakkaraju

    Assuming Batch_Date is of xsd:date type (in YYYY-MM-DD format), you cannot substract dateTime from date. You need to convert it to xsd:dateTime and then subtract. Something like

    xsd:dateTime(concat(string($Batch_Date),'T00:00:00'))-xsd:dayTimeDuration('P30D')

    Also refer this.

    Comment

     

    • Hemanth Lakkaraju

      Assuming Batch_Date is of xsd:date type (in YYYY-MM-DD format), you cannot substract dateTime from date. You need to convert it to xsd:dateTime and then subtract. Something like

      xsd:dateTime(concat(string($Batch_Date),'T00:00:00'))-xsd:dayTimeDuration('P30D')

      Also refer this.

      • Monosij Sinha

        Hi - Thanks for that, but now I have a new error when I try to convert it back. The expression I'm using is below,

         

        xp20:format-dateTime((xsd:dateTime(concat(substring-before($messagecontext_522/../nsmpr0:readFileRecord/nsmpr0:Batch_Date,"/"),"-",substring-before(substring-after($messagecontext_522/../nsmpr0:readFileRecord/nsmpr0:Batch_Date,"/"),"/"),"-",substring-after(substring-after($messagecontext_522/../nsmpr0:readFileRecord/nsmpr0:Batch_Date,"/"),"/"),"T00:00:00")) - xsd:dayTimeDuration(concat("P",substring-after(substring-after($messagecontext_522/../nsmpr0:readFileRecord/nsmpr0:Batch_Date,"/"),"/"),"D"))),"[Y0001]/[M01]/[D01]")

        which gives me:

        XPath expression failed to execute. An error occurs while processing the XPath expression