2

I'm looking for return months in-between dates,

  • Start date: 2022-03-17
  • End Date: 2022-06-17

I am able to get days through the below query.

days-from-duration(xs:date('2022-03-17') - xs:date('2022-06-17'))

But my expected output is: 2022-03,2022-04,2022-05,2022-06

Mads Hansen
  • 63,927
  • 12
  • 112
  • 147
Jayabalan
  • 361
  • 1
  • 7

3 Answers3

2

For each of the days in the fn:days-from-duration(), you can add those days to the start-date and then use fn:format-date() to generate the YYYY-MM value, and then de-dup with distinct-values().

let $start := xs:date('2022-03-17')
let $end := xs:date('2022-06-17')
let $year-months := 
  for $day in 1 to days-from-duration($end - $start ) 
  let $date := $start + xs:dayTimeDuration("P"||$day||"D") 
  return fn:format-date($date, "[Y0001]-[M01]")
return
  distinct-values($year-months)
Mads Hansen
  • 63,927
  • 12
  • 112
  • 147
  • This is a relatively expensive approach, as you construct many year-months, and then dedupe them. – grtjn Sep 17 '22 at 08:36
1

I'd suggest

(a) reducing the two end dates to a "month number" computed as year-from-date()*12 + month-from-date();

(b) for each integer $M in the range start-month-number to end-month-number, convert $M back to a year-month value by doing ($M mod 12) || '-' || ($M idiv 12).

Michael Kay
  • 156,231
  • 11
  • 92
  • 164
0

As suggested by Michael, you should derive a start year-month and end year-month, and then increment with a chosen interval to step through from start to end.

I created a library to help with creating date ranges in pretty much all possible ways: https://github.com/grtjn/ml-date-ranges, although kind of tailored towards use in MarkLogic Search API.

This is the core method though, feel free to use as is, or copy and modify to your needs:

https://github.com/grtjn/ml-date-ranges/blob/master/date-ranges.xqy#L95

HTH!

grtjn
  • 20,254
  • 1
  • 24
  • 35