Relative Dates
Many functions in Carrot (CQL) that accept dates, such as the date() function, will accept Relative Dates, a way of expressing date math.
Relative Dates are expressed as a string of operations, like +3d (add three days), or fiscalYearStart+2q-1q (starting from the beginning of the fiscal year, add two quarters and subtract one day).
Operations are always expressed as + or - followed by a number, followed by one of the following:
- d for days
- w for weeks
- m for months (will preserve end-of-month if the starting point is also EOM)
- q for quarters (will preserve end-of-month if the starting point is also EOM)
- y for years
For example, +3w means ("plus three weeks")
Relative Dates can start with a starting point. If omitted, the starting point will be the current date. For example, +3m means ("three months from the current date") but monthStart+3m means ("three months from the start of the month of the current date")
Valid starting points are:
- today - always returns today's date
- fiscalYearStart
- fiscalQuarterStart
- yearStart
- quarterStart
- monthStart
- weekStart
- fiscalYearEnd
- fiscalQuarterEnd
- yearEnd
- quarterEnd
- monthEnd
- weekEnd
date("+1m") - one month from the current date
date("-30d", "2025-01-31") - returns 2025-01-01
date("+1m-1d") - one month, minus one day, from the current date
date("-1y+1d") - subtract one year, but add one day, to the current date
date("today+1m") - one month from today (equivalent to date("+1m", today))
date("fiscalYearStart+2q") - two quarters from the start of the fiscal year of the current date
date("yearStart+2q") - two quarters from the start of the calendar year of the current date
