CODEWITHSUNDEEP
xquerymarklogicmarklogic-10
Balan
Balan

Reputation: 393

Need to return months in-between dates in XQuery

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

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

Upvotes: 2

Views: 157

Answers (3)

grtjn
grtjn

Reputation: 20414

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!

Upvotes: 0

Mads Hansen
Mads Hansen

Reputation: 66714

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)

Upvotes: 2

Michael Kay
Michael Kay

Reputation: 163262

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).

Upvotes: 1

Related Questions