Topic
  • 2 replies
  • Latest Post - ‏2013-07-03T23:59:56Z by Sigmazen
Sigmazen
Sigmazen
19 Posts

Pinned topic xquery date

‏2013-07-02T21:15:28Z |

Hi

Apologies for the dumb question.

This works:

WITH CM_TRMT_2 (CM_TRMT_ID, TRMT_DTL_XML_TX) AS (
SELECT 1, XMLPARSE(DOCUMENT('<Treatment><EndTs>2015-01-01</EndTs></Treatment>')) FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 3, XMLPARSE(DOCUMENT('<Treatment><EndTs>2012-01-01</EndTs></Treatment>')) FROM SYSIBM.SYSDUMMY1 )
SELECT CM_TRMT_ID, xml2clob(xmlquery('$xml/Treatment/xs:date(EndTs)' PASSING TRMT_DTL_XML_TX as "xml"))
FROM CM_TRMT_2
WHERE XMLEXISTS('$xml/Treatment[xs:date(EndTs) > xs:date("2013-07-01")]' PASSING TRMT_DTL_XML_TX as "xml")

But this doesn't:

WITH CM_TRMT_2 (CM_TRMT_ID, TRMT_DTL_XML_TX) AS (
SELECT 1, XMLPARSE(DOCUMENT('<Treatment><EndTs>2015-01-01</EndTs></Treatment>')) FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 3, XMLPARSE(DOCUMENT('<Treatment><EndTs>2012-01-01</EndTs></Treatment>')) FROM SYSIBM.SYSDUMMY1
)
SELECT CM_TRMT_ID, xml2clob(xmlquery('$xml/Treatment/xs:date(EndTs)' PASSING TRMT_DTL_XML_TX as "xml"))
FROM CM_TRMT_2
WHERE XMLEXISTS('$xml/Treatment[xs:date(EndTs) > current-date]' PASSING TRMT_DTL_XML_TX as "xml")

Any ideas why the current-date I'm using isn't working?

Thanks in advance

Cheers

Simon  

  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: xquery date

    ‏2013-07-03T23:53:54Z  

    Hi Simon,

    not a dumb question at all. But, could you be more specific about "isn't working"?  What exactly is the error message or problem that you are observing when you use current-date?

    You could try using using current-date() instead of current-date. The reason why I think the empty parentheses are needed is that in XQuery, fn:current-date() is a function with zero parameters. In the SQL world, current_date is a special register, not a function.  

    http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.xml.doc/doc/xqrfncda.html

    Tip: Be aware that fn:current-date() gives you a date in the UTC time zone. If you prefer to get the date in the local time zone of your DB2 server, use db2-fn:current-local-date() instead.

    http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.xml.doc/doc/r0055552.html

    Thanks,

    Matthias

     

     

     

  • Sigmazen
    Sigmazen
    19 Posts

    Re: xquery date

    ‏2013-07-03T23:59:56Z  

    Hi Simon,

    not a dumb question at all. But, could you be more specific about "isn't working"?  What exactly is the error message or problem that you are observing when you use current-date?

    You could try using using current-date() instead of current-date. The reason why I think the empty parentheses are needed is that in XQuery, fn:current-date() is a function with zero parameters. In the SQL world, current_date is a special register, not a function.  

    http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.xml.doc/doc/xqrfncda.html

    Tip: Be aware that fn:current-date() gives you a date in the UTC time zone. If you prefer to get the date in the local time zone of your DB2 server, use db2-fn:current-local-date() instead.

    http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.xml.doc/doc/r0055552.html

    Thanks,

    Matthias

     

     

     

    Woohoo :-)

    Parenthesis works a treat ... getting a response now using current-date()

    Sorry about the 'isn't working' generalisation ... basically I meant that it wasn't returning a row with the answer 1.

    Cheers

    Simon