Comparing dates in Oracle XQuery (XMLQuery/XMLTable)

Question

While trying to implement some checks in Oracle database with use of XQuery I need to compare if two dates are the same, but can't do that on obvious manner because casting to xs:date don't really remove time part of value from xs:dateTime.

Query per se seems to work good in another environment (e.g. http://www.xpathtester.com/xquery ).

Is I missed something important, or this case is just a bug and need special workaround (converting to string values for compare, compare years, months and dates of both dates separately and so on)?

A small example ...

Suppose we have a simple XML:

<root>
  <date_value>2015-09-11T15:25:55</date_value>
</root>

and want compare date_value with fixed value xs:date('2015-09-11') ignoring time part.

First, convert content of the node to desired type and remove time part by casting it to xs:date :

xs:date(xs:dateTime($doc/root/date_value))

If we select this value with XMLQuery() while passing document above as $doc, we got expected output:

2015-09-11+00:00

Ok. Seems that time part removed, but comparison fails:

xs:date(xs:dateTime($doc/root/date_value)) eq xs:date('2015-09-11')

returns false, and if we try to look at difference between values in expression instead of comparing them:

xs:date(xs:dateTime($doc/root/date_value)) - xs:date('2015-09-11')

we see 'PT15H25M55S', which are exactly matches time part of date_value.

Query with all expressions above for testing:

select 
  XMLCast(
    XMLQuery( column_value
      passing 
        xmltype(q'[
          <root>
            <date_value>2015-09-11T15:25:55</date_value>
          </root> 
        ]') as "doc"
      returning content
    )
    as varchar2(4000)
  ) result_value,
  column_value  expression
from 
  table(sys.odcivarchar2list(
    q'[ xs:date(xs:dateTime($doc/root/date_value)) ]',
    q'[ xs:date('2015-09-11') ]',
    q'[ xs:date(xs:dateTime($doc/root/date_value)) eq xs:date('2015-09-11') ]',
    q'[ xs:date(xs:dateTime($doc/root/date_value)) - xs:date('2015-09-11') ]'
  ))

Behavior reproduced on this Oracle versions:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0  Production
TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

and

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0  Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Update

Thanks to collapsar and Alex Poole for answers which give me main idea about correct workaround. But trying to explain core of problem I oversimplified our use case which includes some date arithmetic and real-world workaround would look like a query below.

select 
  XMLCast(
    XMLQuery( 
      q'[
        let 
          $date1 := fn:dateTime( 
                      adjust-date-to-timezone(
                        xs:date(xs:dateTime($doc/root/date_value)),
                        ()
                      ),
                      adjust-time-to-timezone( xs:time('00:00'), ())
                    ),
          $date2 := fn:dateTime( 
                      adjust-date-to-timezone(
                        xs:date(xs:dateTime($doc/root/date_value2)),
                        ()
                      ),
                      adjust-time-to-timezone( xs:time('00:00'), ())
                    )
        return
          $date1 + xs:yearMonthDuration('P1Y') - xs:dayTimeDuration('P1D')
          eq
          $date2
      ]'
      passing 
        xmltype(q'[
          <root>
            <date_value>2015-09-11T01:02:03-11:00</date_value>
            <date_value2>2016-09-10T10:20:30+13:00</date_value2>
          </root> 
        ]') as "doc"
      returning content
    )
    as varchar2(4000)
  ) result_value
from 
  dual

collapsar · Accepted Answer · 2015-10-26T18:25:45.587

2

Lifting the literal date value to a dateTime value does the trick (extracting the proper time-of-day offset from the supplied dateTime value ):

 xs:dateTime($doc/root/date_value) eq fn:dateTime(xs:date('2015-09-11'), xs:time(xs:dateTime($doc/root/date_value)))

This solution will also work for inputs lexicalized as dates only.

edited Oct 26 '15 at 18:25

answered Oct 26 '15 at 17:30

collapsar

17,010
4
35
61

Accepted because main idea to replace time part comes from your answer. Please look at updated question text. – ThinkJet Oct 26 '15 at 19:19

Alex Poole · Answer 2 · 2015-10-26T17:57:19.777

You can see from converting back to dateTime that the time has been preserved; which isn't helpful, but I'm not sure if it's a bug or excpected behaviour - I imagine the latter in Oracle's world, and can't see any references to this behaviour in MOS...

You could compare to a dateTime range instead:

select 
  XMLCast(
    XMLQuery( column_value
      passing 
        xmltype(q'[
          <root>
            <date_value>2015-09-11T15:25:55</date_value>
          </root> 
        ]') as "doc"
      returning content
    )
    as varchar2(4000)
  ) result_value,
  column_value  expression
from 
  table(sys.odcivarchar2list(
    q'[ xs:date(xs:dateTime($doc/root/date_value)) ]',
    q'[ xs:date('2015-09-11') ]',
    q'[ xs:date(xs:dateTime($doc/root/date_value)) eq xs:date('2015-09-11') ]',
    q'[ xs:date(xs:dateTime($doc/root/date_value)) - xs:date('2015-09-11') ]',
    q'[ xs:dateTime($doc/root/date_value) ]',
    q'[ xs:dateTime(xs:date(xs:dateTime($doc/root/date_value))) ]',
    q'[ xs:dateTime($doc/root/date_value) ge xs:dateTime('2015-09-11T00:00:00') ]',
    q'[ xs:dateTime($doc/root/date_value) lt xs:dateTime('2015-09-12T00:00:00') ]',
    q'[ xs:dateTime($doc/root/date_value) ge xs:dateTime('2015-09-11T00:00:00')
          and xs:dateTime($doc/root/date_value) lt xs:dateTime('2015-09-12T00:00:00') ]'
  ))
/

Which gives:

RESULT_VALUE                             EXPRESSION                                                                          
---------------------------------------- -------------------------------------------------------------------------------------
 2015-09-11+00:00                         xs:date(xs:dateTime($doc/root/date_value))                                          
 2015-09-11+00:00                         xs:date('2015-09-11')                                                               
false                                     xs:date(xs:dateTime($doc/root/date_value)) eq xs:date('2015-09-11')                 
PT15H25M55S                               xs:date(xs:dateTime($doc/root/date_value)) - xs:date('2015-09-11')                  
 2015-09-11T15:25:55.000000+00:00         xs:dateTime($doc/root/date_value)                                                   
 2015-09-11T15:25:55.000000+00:00         xs:dateTime(xs:date(xs:dateTime($doc/root/date_value)))                             
true                                      xs:dateTime($doc/root/date_value) ge xs:dateTime('2015-09-11T00:00:00')             
true                                      xs:dateTime($doc/root/date_value) lt xs:dateTime('2015-09-12T00:00:00')             
true                                      xs:dateTime($doc/root/date_value) ge xs:dateTime('2015-09-11T00:00:00')             
                                                  and xs:dateTime($doc/root/date_value) lt xs:dateTime('2015-09-12T00:00:00')

You'd need to generate two dates to compare against instead of one, which may or may not be an issue for you, depending on where those are coming from and how you're constructing your real query. Possibly more complicated, but also arguably a bit more explicit, than @collapsar's substring.

Thanks for answer, but initial question was oversimplified by using constant date value and I'm sorry about that. Our checks works against values, so we need to extract date part from values stored in XML to construct range properly. Constructing new `xs:dateTime` with help of `fn:dateTime()` does a trick (because it explicitly construct new instance I think), and after that there are no need for ranges. — ThinkJet, Oct 26 '15 at 19:29

Comparing dates in Oracle XQuery (XMLQuery/XMLTable)

Update

2 Answers2