CODEWITHSUNDEEP
sqlxquery
KlausKerner
KlausKerner

Reputation: 23

Is there an XQuery equivalent to oracle add_months()

The Oracle(SQL) function add_months() adds month to a date but also takes into consideration if a date is on last-day-of-month eg. the YYYY-MM-DD of 2019-02-28 would result in 2019-05-31 with add_months(3)

https://www.oracletutorial.com/oracle-date-functions/oracle-add_months/

I am to port the same functionality to xquery and I already learned of functx:add-months(date, int) and functx:last-day-of-month(date).

I guess I could make my own function out of the 2, but there is a much bigger algorithm at work here so I would prefer this thing already available in a tested and working manner and a solid official function...

so long story short was this already forged in a function library that I don't know of or do I have to do this on my own?

Upvotes: 0

Views: 327

Answers (2)

Michael Kay
Michael Kay

Reputation: 163262

The "+" operator allows you to add a yearMonthDuration to a date. For example

xs:date('2019-02-28') + xs:yearMonthDuration('P3M')

I don't know whether the semantics are exactly the same as the Oracle SQL function. They are given at https://www.w3.org/TR/xpath-functions-31/#func-add-dayTimeDuration-to-date

LATER

It looks as if the Oracle function special-cases the last day of the month, so adding or subtracting months to a date that is the last day of the month gives the last day of a different month.

The W3C algorithm (which is defined by reference to https://www.w3.org/TR/xmlschema-2/#adding-durations-to-dateTimes) does it differently: it adjusts the year and month numbers of the (year, month, day) and if the resulting day is out of range for the resulting month, pins it to the last day of that month.

You should be able to reproduce the behaviour of the Oracle function with a user-defined function. This might invoke a couple of helper functions:

function d:is-last-day-of-month($date as xs:date) as xs:boolean {
  month-from-date($date) != month-from-date($date + xs:dayTimeDuration('P1D')
}

function d:last-date-in-month($date as xs:date) as xs:date {
  if (d:is-last-day-of-month($date))
  then $date
  else d:last-date-in-month($date + xs:dayTimeDuration('P1D'))
}

function d:add-months($date as xs:date, $months as xs:integer) {
  if (d:is-last-day-of-month($date))
  then d:last-date-in-month($date + $months * xs:dayTimeDuration('P1M'))
  else $date + $months * xs:dayTimeDuration('P1M')
}

Upvotes: 1

KlausKerner
KlausKerner

Reputation: 23

Ok I think I just solved it with the following custom function that I now use in the algorithm instead of the official xquery-> functx:add-months(date,int)

declare function local:addMonths($input as xs:date?, $amount as xs:integer?)
as xs:date?
{
  let $outputN := functx:add-months($input,$amount)
  let $outputLD := functx:last-day-of-month(functx:add-months($input,$amount))
  return if($input = functx:last-day-of-month($input)) then $outputLD else $outputN
};

Needs some more testing as the whole algorithm behind these modifications is much more difficult but a first run looked fine

The purpose of the whole algorithm was to change date attributes in an .xml by moving the dates eg. date="2018-11-28" (usually) 3 months into the future

The dates were supposed to stay relative to the source, meaning moving "2018-11-28" 3 months forward should result in:

"2019-02-26" (2 days before the final day of the month!)

Upvotes: 0

Related Questions