1

SQL query:

SELECT 
    ROUND((CAST(DATEATTRIBUTE2 AS DATE) - CAST(DATEATTRIBUTE1 AS DATE)) * 86400) AS result 
FROM TEST_TABLE;

Both DATEATTRIBUTE1 and DATEATTRIBUTE2 are of TIMESTAMP type.

I have tried and come up with the following XQuery :

fn:days-from-duration(fn:subtract-dateTimes-yielding-dayTimeDuration(
     $P_REQUEST/ns1:DATEATTRIBUTE2,$P_REQUEST/ns1:DATEATTRIBUTE1))*86400

But this fails for cases when the dates are same but there is difference in time.E.g. When DATEATTRIBUTE1 is 2017-02-23T01:17:18.0000 and DATEATTRIBUTE2 is 2017-02-23T01:17:20.7550 the SQL query returns 2 while XQuery returns 0.

Thanks in advance for the help!

Ghislain Fourny
  • 6,971
  • 1
  • 30
  • 37
boredDev
  • 317
  • 3
  • 18

1 Answers1

1

If I correctly understand, you need the total number of seconds between the two dateTimes. You can do it this way:

floor(
  ($P_REQUEST/ns1:DATEATTRIBUTE2 - $P_REQUEST/ns1:DATEATTRIBUTE1)
  div xs:dayTimeDuration('PT1S')
)

That is, substracting them -- you can use the - operator -- and then dividing the obtained duration by the duration of 1s, then rounding down.

It yields 2 for the example given in the question.

For further reference, there is a functx function documented here that suggests this way.

Ghislain Fourny
  • 6,971
  • 1
  • 30
  • 37
  • Tried using this but it shows {err}XQ0017: "{http://www.w3.org/2001/XMLSchema}dayTimeDuration": unknown function (or number of arguments (1) is wrong) – boredDev Feb 27 '17 at 13:49
  • That's strange, maybe specific to Oracle if they do not support all of XQuery. How about `"PT1S" cast as xs:dayTimeDuration` instead? xs:dayTimeDuration is actually XML Schema 1.1 (but also XQuery 1.0), so if it's not supported, may be `"PT1S" cast as xs:duration` or `xs:duration("PT1S")`? – Ghislain Fourny Feb 27 '17 at 14:23
  • Hi @Ghislain ! It finally worked by using xdt:dayTimeDuration instead of xs:dayTimeDuration and the functx library looked pretty cool so tried importing it but sadly OSB gave an `XQ0016:module feature not supported (sorry)` error. – boredDev Mar 01 '17 at 14:51
  • That's good to hear, it was only a matter of prefix binding then. functx is not an official part of the XQuery standard, some implementations support it out of the box, some others not. However, the functx website provides the code so you can directly reuse it in an implementation that doesn't support it. – Ghislain Fourny Mar 01 '17 at 15:00