Forum Stats

  • 3,854,668 Users
  • 2,264,397 Discussions
  • 7,905,749 Comments

Discussions

How to convert xs:date to xs:dateTime

James.Tran
James.Tran Member Posts: 87
edited Jun 25, 2015 10:53PM in XQuery

In my application, there are the following 2 cases I need to handle:

  1. I have a "xs:date" and I need to convert it to "xs:dateTime"
  2. I have a "xs:date" and a "xs:time" and I need to combine them into "xs:dateTime"

Following the answer for this question on Stackoverflow, I tried both "<span class="pln">xs:dateTime($date)</span>" and  "<span class="pln">fn:dateTime($date, $time)</span>" but none of them worked.

When I used "<span class="pln">xs:dateTime($date)</span>" to handle the 1st case, I got the following exception when OSB reaches the conversion step for the date:

OSB Replace action failed updating variable "body": Error parsing XML: {err}XP0021: "2014-12-09": can not cast to {http://www.w3.org/2001/XMLSchema}dateTime: error: date: Invalid date value: wrong type: 2014-12-09

When I used "<span class="pln">fn:dateTime($date, $time)</span>", the transformation is not even accepted by the IDE. It displays the following error:

line 113, column 42: "{http://www.w3.org/2004/07/xpath-functions}dateTime" unknown function (or number of arguments (2) is wrong

At the moment, as a workaround, I am using "concat" to combine the date and time fields in the 2nd case and to append "T00:00:00" to handle the 1st case but I feel this is not a good approach.

I'd be very grateful if you could show me how I can properly convert date fields into dateTime format.

Best Answer

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Jun 25, 2015 8:20AM Answer ✓
    At the moment, I am using OSB 11g with the latest bundled patch v11.1.1.7.4 applied.
    

    Found in the documentation that, indeed, only the 2004-07 WD is implemented in this version :

    https://docs.oracle.com/cd/E28280_01/admin.1111/e15867/xquery.htm#OSBAG444

    According to the specs, the cast from xs:date to xs:dateTime should work :

    XQuery 1.0 and XPath 2.0 Functions and Operators

    But as I mentioned earlier, it may just be that you're not passing the correct datatype.

    If the data is not schema-based, then sch:jdOriginalPromisedDeliveryDate will be considered as of xdt:untypedAtomic type, and cannot be cast to anything but xs:string.

    Does this work better :

    <ns2:expectedDeliveryDate>{ xs:date($jdOriginalPromisedDeliveryDate) cast as xs:dateTime }</ns2:expectedDeliveryDate>
    

    ?

Answers

  • Marco Gralike
    Marco Gralike Member Posts: 4,491 Silver Trophy
    edited Jun 23, 2015 4:08AM

    Please give us your SQL/XQuery statement and database version (ALL digits please, not just "11gR2" or something)

  • Marco Gralike
    Marco Gralike Member Posts: 4,491 Silver Trophy
    edited Jun 23, 2015 4:11AM

    In the meantime the following might help?

    http://www.liberidu.com/blog/?s=datetime

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Jun 23, 2015 6:28AM
    I have a "xs:date" and I need to convert it to "xs:dateTime"
    

    You cannot use the xs:dateTime constructor since it expects an argument with the time component already there.

    Use the cast operator in this case, it should work :

    let $d1 as xs:date := xs:date("2014-09-12")
    return $d1 cast as xs:dateTime
    

    returns an xs:dateTime instance with value 2014-09-12T00:00:00

    If it doesn't work, that means there's a bug in OSB XQuery implementation.

    I have a "xs:date" and a "xs:time" and I need to combine them into "xs:dateTime"
    

    Again, the standard XPath function fn:dateTime() should work here.

    Make sure you're passing arguments of the correct datatypes :

    let $d1 := xs:date("2014-09-12")
    let $t1 := xs:time("15:00:35")
    return fn:dateTime($d1, $t1)
    

    returns an xs:dateTime instance with value 2014-09-12T15:00:35

    James.Tran
  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Jun 23, 2015 6:52AM

    Just noticed something interesting from the error message :

    line 113, column 42: "{http://www.w3.org/2004/07/xpath-functions}dateTime" unknown function (or number of arguments (2) is wrong
    
    
    

    The namespace uri is that of the July 2004 Working Draft, which does not include the fn:dateTime function yet :

    http://www.w3.org/TR/2004/WD-xpath-functions-20040723/#namespace-prefixes

    The latest namespace, bound to the current XQuery and XPath 2.0 Functions specs is '<span style="font-family: arial,helvetica,sans-serif;"><a class="jive-link-external-small" href="http://www.w3.org/2005/xpath-functions" rel="nofollow">http://www.w3.org/2005/xpath-functions</a><span>' and does contain the </span><span style="font-family: courier new,courier;">fn:dateTime()</span> function : </span>

    <span style="font-family: arial,helvetica,sans-serif;"><a href="http://www.w3.org/TR/xpath-functions/#func-dateTime" title="http://www.w3.org/TR/xpath-functions/#func-dateTime">http://www.w3.org/TR/xpath-functions/#func-dateTime</a></span>

    So the error you get is 'normal', but now the obvious question is why OSB does not implement the latest standard?

    What version are you using?

    James.Tran
  • James.Tran
    James.Tran Member Posts: 87
    edited Jun 24, 2015 10:17PM

    When I run "opatch lsinventory" in the command prompt, the database version is shown to be "Oracle Database 11g - v11.2.0.1.0".

    Regarding the SQL/XQuery version, I have no idea where to get this information . Please give me some hints if possible!

  • James.Tran
    James.Tran Member Posts: 87
    edited Jun 24, 2015 10:42PM

    Thank you for the insightful observation!

    At the moment, I am using OSB 11g with the latest bundled patch v11.1.1.7.4 applied. To implement artifacts for OSB, I am using OEPE for OSB 11g. Please find more information from the pictures below.

    Capture.PNG

    Capture.PNG

    On the side note, I did try to import the 2005 namespace manually as following:

    declare namespace func = "<span style="font-family: arial,helvetica,sans-serif;"><a class="jive-link-external-small" href="http://www.w3.org/2005/xpath-functions" rel="nofollow">http://www.w3.org/2005/xpath-functions</a></span>";
    
    

    However, the same error was thrown in OEPE.

    line 113, column 42: "{<span style="font-family: arial,helvetica,sans-serif;"><a class="jive-link-external-small" href="http://www.w3.org/2005/xpath-functions" rel="nofollow">http://www.w3.org/2005/xpath-functions</a></span>}dateTime" unknown function (or number of arguments (2) is wrong
    
    

    Please let me know if you need any further information

  • James.Tran
    James.Tran Member Posts: 87
    edited Jun 24, 2015 10:41PM

    I've just tried to use "cast as xs:dateTime" as you suggested but it still didn't work.

    To be more precise, in the sample inbound message, I have the following element:

    <sch:jdOriginalPromisedDeliveryDate>2014-12-09</sch:jdOriginalPromisedDeliveryDate>
    

    I tried to map this element to my "expectedDeliveryDate" element as following:

    {
         for $jdOriginalPromisedDeliveryDate in $jdeResponse/ns0:event/ns0:body/ns0:detail_D4302470A/ns0:jdOriginalPromisedDeliveryDate
         return
              <ns2:expectedDeliveryDate>{ $jdOriginalPromisedDeliveryDate cast as xs:dateTime }</ns2:expectedDeliveryDate>
    }
    

    In the end, OSB still throw the same error

      OSB Replace action failed updating variable "body": Error parsing XML: {err}XP0021: "2014-12-09": can not cast to {http://www.w3.org/2001/XMLSchema}dateTime: error: date: Invalid date value: wrong type: 2014-12-09
    
  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Jun 25, 2015 8:20AM Answer ✓
    At the moment, I am using OSB 11g with the latest bundled patch v11.1.1.7.4 applied.
    

    Found in the documentation that, indeed, only the 2004-07 WD is implemented in this version :

    https://docs.oracle.com/cd/E28280_01/admin.1111/e15867/xquery.htm#OSBAG444

    According to the specs, the cast from xs:date to xs:dateTime should work :

    XQuery 1.0 and XPath 2.0 Functions and Operators

    But as I mentioned earlier, it may just be that you're not passing the correct datatype.

    If the data is not schema-based, then sch:jdOriginalPromisedDeliveryDate will be considered as of xdt:untypedAtomic type, and cannot be cast to anything but xs:string.

    Does this work better :

    <ns2:expectedDeliveryDate>{ xs:date($jdOriginalPromisedDeliveryDate) cast as xs:dateTime }</ns2:expectedDeliveryDate>
    

    ?

  • James.Tran
    James.Tran Member Posts: 87
    edited Jun 25, 2015 10:53PM

    Ouch, then I guess I have no choice but to use concat to combine date and time .

    Regarding the mapping from "xs:date" to "xs:dateTime", your suggestion worked this time . Thanks a lot. The weird thing is that sch:jdOriginalPromisedDeliveryDate is defined as "xs:date" in the XSD.

    <xsd:element minOccurs="0" name="jdOriginalPromisedDeliveryDate" type="xsd:date"/>
    
    

    To be precise, the message is retrieved by a proxy service from a JMS queue. When defining the proxy service, I've already selected an element in the XSD file as the message format. By right, I think OSB should have been able to detect the data type correctly.

    UPDATE:

    After some more testing, I found that even though "xs:date($jdOriginalPromisedDeliveryDate) cast as xs:dateTime" worked as expected, OEPE will automatically remove the "cast as xs:dateTime" portion when I drag and drop to do mapping for other fields. So far, I don't observe the same issue with the concat method.

This discussion has been closed.