Forums

Articles
Create
cancel
Showing results for 
Search instead for 
Did you mean: 

JQL Relative Date Functions

Stuart Steedman
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
June 10, 2025

I'm just answering my own question here...

We run some of our reports for weeks that run Thursday-Wednesday.  Using standard JQL functions the basic functions sort of work, but when the week moves on on a Sunday, the reports are now a week ahead until the following Thursday.

None of the built-in JQL date functions allow any funny business.  Trust me, I've tried it.  So this is the solution I came up with.  Not exactly easy to maintain...

If you have a week that runs (inclusively) Monday-Sunday or Tuesday-Monday etc you will have to modify the following...

    Current Week  Coming Week  Last Week  Prior Week
  00project = CCB and (77project = CCB and (-7-7project = CCB and (-14-14project = CCB and (
SUNMON11(due = startofday(1d) and due = startOfWeek(1d)) or88(due = startofday(8d) and due = startOfWeek(8d)) or-6-6(due = startofday(-6d) and due = startOfWeek(-6d)) or-13-13(due = startofday(-13d) and due = startOfWeek(-13d)) or
TUE22(due = startofday(2d) and due = startOfWeek(2d)) or99(due = startofday(9d) and due = startOfWeek(9d)) or-5-5(due = startofday(-5d) and due = startOfWeek(-5d)) or-12-12(due = startofday(-12d) and due = startOfWeek(-12d)) or
WED33(due = startofday(3d) and due = startOfWeek(3d)) or1010(due = startofday(10d) and due = startOfWeek(10d)) or-4-4(due = startofday(-4d) and due = startOfWeek(-4d)) or-11-11(due = startofday(-11d) and due = startOfWeek(-11d)) or
THU-3-3(due = startofday(-3d) and due = startOfWeek(-3d)) or44(due = startofday(4d) and due = startOfWeek(4d)) or-10-10(due = startofday(-10d) and due = startOfWeek(-10d)) or-17-17(due = startofday(-17d) and due = startOfWeek(-17d)) or
FRI-2-2(due = startofday(-2d) and due = startOfWeek(-2d)) or55(due = startofday(5d) and due = startOfWeek(5d)) or-9-9(due = startofday(-9d) and due = startOfWeek(-9d)) or-16-16(due = startofday(-16d) and due = startOfWeek(-16d)) or
SAT-1-1(due = startofday(-1d) and due = startOfWeek(-1d)) or66(due = startofday(6d) and due = startOfWeek(6d)) or-8-8(due = startofday(-8d) and due = startOfWeek(-8d)) or-15-15(due = startofday(-15d) and due = startOfWeek(-15d)) or
SUN00(due = startofday(0d) and due = startOfWeek(0d)) or77(due = startofday(7d) and due = startOfWeek(7d)) or-7-7(due = startofday(-7d) and due = startOfWeek(-7d)) or-14-14(due = startofday(-14d) and due = startOfWeek(-14d)) or
MONMON01(due = startofday(0d) and due = startOfWeek(1d)) or78(due = startofday(7d) and due = startOfWeek(8d)) or-7-6(due = startofday(-7d) and due = startOfWeek(-6d)) or-14-13(due = startofday(-14d) and due = startOfWeek(-13d)) or
TUE12(due = startofday(1d) and due = startOfWeek(2d)) or89(due = startofday(8d) and due = startOfWeek(9d)) or-6-5(due = startofday(-6d) and due = startOfWeek(-5d)) or-13-12(due = startofday(-13d) and due = startOfWeek(-12d)) or
WED23(due = startofday(2d) and due = startOfWeek(3d)) or910(due = startofday(9d) and due = startOfWeek(10d)) or-5-4(due = startofday(-5d) and due = startOfWeek(-4d)) or-12-11(due = startofday(-12d) and due = startOfWeek(-11d)) or
THU-4-3(due = startofday(-4d) and due = startOfWeek(-3d)) or34(due = startofday(3d) and due = startOfWeek(4d)) or-11-10(due = startofday(-11d) and due = startOfWeek(-10d)) or-18-17(due = startofday(-18d) and due = startOfWeek(-17d)) or
FRI-3-2(due = startofday(-3d) and due = startOfWeek(-2d)) or45(due = startofday(4d) and due = startOfWeek(5d)) or-10-9(due = startofday(-10d) and due = startOfWeek(-9d)) or-17-16(due = startofday(-17d) and due = startOfWeek(-16d)) or
SAT-2-1(due = startofday(-2d) and due = startOfWeek(-1d)) or56(due = startofday(5d) and due = startOfWeek(6d)) or-9-8(due = startofday(-9d) and due = startOfWeek(-8d)) or-16-15(due = startofday(-16d) and due = startOfWeek(-15d)) or
SUN-10(due = startofday(-1d) and due = startOfWeek(0d)) or67(due = startofday(6d) and due = startOfWeek(7d)) or-8-7(due = startofday(-8d) and due = startOfWeek(-7d)) or-15-14(due = startofday(-15d) and due = startOfWeek(-14d)) or
TUEMON-11(due = startofday(-1d) and due = startOfWeek(1d)) or68(due = startofday(6d) and due = startOfWeek(8d)) or-8-6(due = startofday(-8d) and due = startOfWeek(-6d)) or-15-13(due = startofday(-15d) and due = startOfWeek(-13d)) or
TUE02(due = startofday(0d) and due = startOfWeek(2d)) or79(due = startofday(7d) and due = startOfWeek(9d)) or-7-5(due = startofday(-7d) and due = startOfWeek(-5d)) or-14-12(due = startofday(-14d) and due = startOfWeek(-12d)) or
WED13(due = startofday(1d) and due = startOfWeek(3d)) or810(due = startofday(8d) and due = startOfWeek(10d)) or-6-4(due = startofday(-6d) and due = startOfWeek(-4d)) or-13-11(due = startofday(-13d) and due = startOfWeek(-11d)) or
THU-5-3(due = startofday(-5d) and due = startOfWeek(-3d)) or24(due = startofday(2d) and due = startOfWeek(4d)) or-12-10(due = startofday(-12d) and due = startOfWeek(-10d)) or-19-17(due = startofday(-19d) and due = startOfWeek(-17d)) or
FRI-4-2(due = startofday(-4d) and due = startOfWeek(-2d)) or35(due = startofday(3d) and due = startOfWeek(5d)) or-11-9(due = startofday(-11d) and due = startOfWeek(-9d)) or-18-16(due = startofday(-18d) and due = startOfWeek(-16d)) or
SAT-3-1(due = startofday(-3d) and due = startOfWeek(-1d)) or46(due = startofday(4d) and due = startOfWeek(6d)) or-10-8(due = startofday(-10d) and due = startOfWeek(-8d)) or-17-15(due = startofday(-17d) and due = startOfWeek(-15d)) or
SUN-20(due = startofday(-2d) and due = startOfWeek(0d)) or57(due = startofday(5d) and due = startOfWeek(7d)) or-9-7(due = startofday(-9d) and due = startOfWeek(-7d)) or-16-14(due = startofday(-16d) and due = startOfWeek(-14d)) or
WEDMON-21(due = startofday(-2d) and due = startOfWeek(1d)) or58(due = startofday(5d) and due = startOfWeek(8d)) or-9-6(due = startofday(-9d) and due = startOfWeek(-6d)) or-16-13(due = startofday(-16d) and due = startOfWeek(-13d)) or
TUE-12(due = startofday(-1d) and due = startOfWeek(2d)) or69(due = startofday(6d) and due = startOfWeek(9d)) or-8-5(due = startofday(-8d) and due = startOfWeek(-5d)) or-15-12(due = startofday(-15d) and due = startOfWeek(-12d)) or
WED03(due = startofday(0d) and due = startOfWeek(3d)) or710(due = startofday(7d) and due = startOfWeek(10d)) or-7-4(due = startofday(-7d) and due = startOfWeek(-4d)) or-14-11(due = startofday(-14d) and due = startOfWeek(-11d)) or
THU-6-3(due = startofday(-6d) and due = startOfWeek(-3d)) or14(due = startofday(1d) and due = startOfWeek(4d)) or-13-10(due = startofday(-13d) and due = startOfWeek(-10d)) or-20-17(due = startofday(-20d) and due = startOfWeek(-17d)) or
FRI-5-2(due = startofday(-5d) and due = startOfWeek(-2d)) or25(due = startofday(2d) and due = startOfWeek(5d)) or-12-9(due = startofday(-12d) and due = startOfWeek(-9d)) or-19-16(due = startofday(-19d) and due = startOfWeek(-16d)) or
SAT-4-1(due = startofday(-4d) and due = startOfWeek(-1d)) or36(due = startofday(3d) and due = startOfWeek(6d)) or-11-8(due = startofday(-11d) and due = startOfWeek(-8d)) or-18-15(due = startofday(-18d) and due = startOfWeek(-15d)) or
SUN-30(due = startofday(-3d) and due = startOfWeek(0d)) or47(due = startofday(4d) and due = startOfWeek(7d)) or-10-7(due = startofday(-10d) and due = startOfWeek(-7d)) or-17-14(due = startofday(-17d) and due = startOfWeek(-14d)) or
THUMON48(due = startofday(4d) and due = startOfWeek(8d)) or1115(due = startofday(11d) and due = startOfWeek(15d)) or-31(due = startofday(-3d) and due = startOfWeek(1d)) or-10-6(due = startofday(-10d) and due = startOfWeek(-6d)) or
TUE59(due = startofday(5d) and due = startOfWeek(9d)) or1216(due = startofday(12d) and due = startOfWeek(16d)) or-22(due = startofday(-2d) and due = startOfWeek(2d)) or-9-5(due = startofday(-9d) and due = startOfWeek(-5d)) or
WED610(due = startofday(6d) and due = startOfWeek(10d)) or1317(due = startofday(13d) and due = startOfWeek(17d)) or-13(due = startofday(-1d) and due = startOfWeek(3d)) or-8-4(due = startofday(-8d) and due = startOfWeek(-4d)) or
THU04(due = startofday(0d) and due = startOfWeek(4d)) or711(due = startofday(7d) and due = startOfWeek(11d)) or-7-3(due = startofday(-7d) and due = startOfWeek(-3d)) or-14-10(due = startofday(-14d) and due = startOfWeek(-10d)) or
FRI15(due = startofday(1d) and due = startOfWeek(5d)) or812(due = startofday(8d) and due = startOfWeek(12d)) or-6-2(due = startofday(-6d) and due = startOfWeek(-2d)) or-13-9(due = startofday(-13d) and due = startOfWeek(-9d)) or
SAT26(due = startofday(2d) and due = startOfWeek(6d)) or913(due = startofday(9d) and due = startOfWeek(13d)) or-5-1(due = startofday(-5d) and due = startOfWeek(-1d)) or-12-8(due = startofday(-12d) and due = startOfWeek(-8d)) or
SUN37(due = startofday(3d) and due = startOfWeek(7d)) or1014(due = startofday(10d) and due = startOfWeek(14d)) or-40(due = startofday(-4d) and due = startOfWeek(0d)) or-11-7(due = startofday(-11d) and due = startOfWeek(-7d)) or
FRIMON38(due = startofday(3d) and due = startOfWeek(8d)) or1015(due = startofday(10d) and due = startOfWeek(15d)) or-41(due = startofday(-4d) and due = startOfWeek(1d)) or-11-6(due = startofday(-11d) and due = startOfWeek(-6d)) or
TUE49(due = startofday(4d) and due = startOfWeek(9d)) or1116(due = startofday(11d) and due = startOfWeek(16d)) or-32(due = startofday(-3d) and due = startOfWeek(2d)) or-10-5(due = startofday(-10d) and due = startOfWeek(-5d)) or
WED510(due = startofday(5d) and due = startOfWeek(10d)) or1217(due = startofday(12d) and due = startOfWeek(17d)) or-23(due = startofday(-2d) and due = startOfWeek(3d)) or-9-4(due = startofday(-9d) and due = startOfWeek(-4d)) or
THU-14(due = startofday(-1d) and due = startOfWeek(4d)) or611(due = startofday(6d) and due = startOfWeek(11d)) or-8-3(due = startofday(-8d) and due = startOfWeek(-3d)) or-15-10(due = startofday(-15d) and due = startOfWeek(-10d)) or
FRI05(due = startofday(0d) and due = startOfWeek(5d)) or712(due = startofday(7d) and due = startOfWeek(12d)) or-7-2(due = startofday(-7d) and due = startOfWeek(-2d)) or-14-9(due = startofday(-14d) and due = startOfWeek(-9d)) or
SAT16(due = startofday(1d) and due = startOfWeek(6d)) or813(due = startofday(8d) and due = startOfWeek(13d)) or-6-1(due = startofday(-6d) and due = startOfWeek(-1d)) or-13-8(due = startofday(-13d) and due = startOfWeek(-8d)) or
SUN27(due = startofday(2d) and due = startOfWeek(7d)) or914(due = startofday(9d) and due = startOfWeek(14d)) or-50(due = startofday(-5d) and due = startOfWeek(0d)) or-12-7(due = startofday(-12d) and due = startOfWeek(-7d)) or
SATMON28(due = startofday(2d) and due = startOfWeek(8d)) or915(due = startofday(9d) and due = startOfWeek(15d)) or-51(due = startofday(-5d) and due = startOfWeek(1d)) or-12-6(due = startofday(-12d) and due = startOfWeek(-6d)) or
TUE39(due = startofday(3d) and due = startOfWeek(9d)) or1016(due = startofday(10d) and due = startOfWeek(16d)) or-42(due = startofday(-4d) and due = startOfWeek(2d)) or-11-5(due = startofday(-11d) and due = startOfWeek(-5d)) or
WED410(due = startofday(4d) and due = startOfWeek(10d)) or1117(due = startofday(11d) and due = startOfWeek(17d)) or-33(due = startofday(-3d) and due = startOfWeek(3d)) or-10-4(due = startofday(-10d) and due = startOfWeek(-4d)) or
THU-24(due = startofday(-2d) and due = startOfWeek(4d)) or511(due = startofday(5d) and due = startOfWeek(11d)) or-9-3(due = startofday(-9d) and due = startOfWeek(-3d)) or-16-10(due = startofday(-16d) and due = startOfWeek(-10d)) or
FRI-15(due = startofday(-1d) and due = startOfWeek(5d)) or612(due = startofday(6d) and due = startOfWeek(12d)) or-8-2(due = startofday(-8d) and due = startOfWeek(-2d)) or-15-9(due = startofday(-15d) and due = startOfWeek(-9d)) or
SAT06(due = startofday(0d) and due = startOfWeek(6d)) or713(due = startofday(7d) and due = startOfWeek(13d)) or-7-1(due = startofday(-7d) and due = startOfWeek(-1d)) or-14-8(due = startofday(-14d) and due = startOfWeek(-8d)) or
SUN17(due = startofday(1d) and due = startOfWeek(7d)) or814(due = startofday(8d) and due = startOfWeek(14d)) or-60(due = startofday(-6d) and due = startOfWeek(0d)) or-13-7(due = startofday(-13d) and due = startOfWeek(-7d)) or
          
    ORDER BY due ASC, key ASC  ORDER BY due ASC, key ASC  ORDER BY due ASC, key ASC  ORDER BY due ASC, key ASC

1 answer

0 votes
Stuart Steedman
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
June 10, 2025

Oh, by the way you need to remove the "or" from the last row - these were generated in Excel and I forgot to update the last row...

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
ENTERPRISE
TAGS
AUG Leaders

Atlassian Community Events