Skip to main content

Understanding relative date ranges

Don't forget to check out our JSON RESTful APIs, they can help you utilize and extend Quickbase with ease.

You can write Quickbase queries to find records where certain dates fall within relative date ranges. A relative date range is a period of time that is relative to the current date. (Last Week, Next Month, and During the Next 3 Quarters are examples of relative date ranges.

Using relative date ranges allows you to create reports that you can run periodically without needing to alter your query. For example, you may want to create a report that you run monthly, that shows all tasks with completion dates that occurred during the last month.

For information about working with dates in regular Quickbase queries, see Working with date and time.

The IR and XIR operators

You can write a query using the IR (Is during) operator to find all records where the completion date or date/time field falls within the relative date range of Last Month.

You can also use the XIR (is not in range) operator to find records with date or date/time fields that do not fall within the specified dynamic date range.

You can use either of these operators with any date or date/time field in your applications. You can build queries that use dynamic date ranges in the following categories:

  • Days

  • Weeks

  • Months

  • Quarters

  • Years

Relative date ranges: Days

A day starts at 12:00:00 a.m. and continues for 24 hours, ending at 11:59:59 p.m. (Start and end times are always based on the application's time zone.)

Operator Description

today

 

Starts at 12:00:00 a.m. on the current day and continues for 24 hours.

XML example:

<query>{7.IR.'today'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'today'}

This example returns:

All records where the date or date/time in fid 7 is equal to the current day.

tomorrow

Starts at 12:00:00 a.m. on the day after the current day and continues for 24 hours.

XML example:

<query>{7.IR.'tomorrow'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'tomorrow'}

This example returns:

All records where the date or date/time in fid 7 is equal to tomorrow's date.

yesterday

Starts at 12:00:00 a.m. on the day before the current day and continues for 24 hours.

XML example:

<query>{7.IR.'yesterday'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'yesterday'}

This example returns:

All records where the date or date/time in fid 7 is equal to yesterday's date.

next N days

Starts at 12:00:00 a.m. tomorrow and continues for the next N days. (The range does not include today.)

XML example:

<query>{7.IR.'next 5 d'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'next+5+d'}

This example returns:

All records where the date or date/time in fid 7 falls within the 5 consecutive days starting at 12:00.00 a.m. tomorrow.

last N days

Starts at 12:00:00 a.m. N days before the current day and continues to the end of yesterday. (The range does not include the current day.)

XML example:

<query>{7.IR.'last 5 d'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'last+5+d'}

This example returns:

All records where the date or date/time in fid 7 falls within the 5 consecutive days that end on 11:59:59 yesterday.

Relative date ranges: Weeks

Each application manager can define the day that starts a week for each application. A week starts at 12:00:00 a.m. on the specified start date and continues 7 consecutive days, ending at 11:59:59 on the seventh day. (Start and end times are always based on the application's time zone.)

By default, a week starts at 12:00:00 a.m. on Sunday and continues for 7 complete days (ending at 11:59:59 on Saturday.)

See the Quickbase online help to learn how to set the day of the week for the application.

Operator Description

this wk

Starts at 12:00:00 a.m. on the first day of the current week and continues for seven days.

XML example:

<query>{7.IR.'this wk'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'this+wk'}

This example returns:

All records where the date in fid 7 occurs within the time period that starts at 12:00:00 a.m. on the first day of the current week and continues for seven days.

next wk

The 7-day period that starts immediately after the current week ends.

Next week starts at 12:00:00 a.m. the first day of the week after the current week and continues for 7 days.

XML example:

<query>{7.IR.'next wk'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'next+wk'}

This example returns:

All records where the date or date/time in fid 7 occurs within the 7-day period that starts at 12:00:00 a.m. on the first day of the week after the current week.

last wk

The 7-day period that ends immediately before the current week starts.

Last week starts at 12:00:00 a.m. on the first day of the week that precedes the current week and continues for seven days.

XML example:

<query>{7.IR.'last wk'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'last+wk'}

This example returns:

All records where the date  or date/time in fid 7 occurs within the 7-day period that ends at 11:59:59 on the last day of the week before the current week.

next N wk

 

Starts at 12:00:00 a.m. on the first day of next week and continues for the next N weeks.

XML example:

<query>{7.IR.'next 5 wk'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'next+5+wk'}

This example returns:

All records where the date or date/time in fid 7 occurs within the 5 consecutive weeks starting next week.

last N wk

Starts at 12:00:00 a.m. N weeks before the current week and continues for n weeks.

XML example:

<query>{7.IR.'last 5 wk'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'last+5+wk'}

This example returns:

All records where the date or date/time in fid 7 falls within the 5 consecutive weeks that end immediately before the current week begins.

Dynamic date ranges: Months

In Quickbase, months are defined according to the Western calendar. A month starts at 12:00:00 a.m. on the first day of a month and continues until 11:59:59 on the last day of the month. (Start and end times are always based on the application's time zone.)

Operator Description

this mon

Starts at 12:00:00 a.m. on the first day of the current month and continues until 11:59:59 on the last day of the month.

XML example:

<query>{7.IR.'this mon'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'this+mon'}

This example returns:

All records where the date or date/time in fid 7 occurs within the current month.

next mon

Starts at 12:00:00 a.m. on the first day of the month that follows the current month and continues until 11:59:59 on the last day of the month.

XML example:

<query>{7.IR.'next mon'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'next+mon'}

This example returns:

All records where the date or date/time in fid 7 occurs within the month immediately following the current month

last mon

Starts at 12:00:00 a.m. on the first day of the month that precedes the current month and continues until 11:59:59 on the last day of the month.

XML example:

<query>{7.IR.'last mon'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'last+mon'}

This example returns:

All records where the date or date/time in fid 7 occurs within the month immediately preceding the current month.

next N mon

 

Starts at 12:00:00 a.m. on first day of the next month and continues for  N months. (The range does not include the current month.)

XML example:

<query>{7.IR.'next 5 mon'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'next+5+mon'}

This example returns:

All records where the date or date/time in fid 7 occurs within the 5 consecutive months starting the on the first day of next month.

last N mon

Starts at 12:00:00 a.m. N months before the current month and continues for N months. (The range does not include the current month.)

XML example:

<query>{7.IR.'last 5 mon'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'last+5+mon'}

This example returns:

All records where the date or date/time in fid 7 falls within the 5 consecutive months that end immediately before the current month begins.

Relative date ranges: Quarters

Each year is divided into 3-month long quarters. In Quickbase, quarters are defined as follows:

  • Q1--January through March

  • Q2--April through June

  • Q3--July through September

  • Q4--October through December

 (Start and end times are always based on the application's time zone.)

Operator Description

this q

Starts at 12:00:00 a.m. on the first day of the current quarter and continues for three months, ending at 11:59:59 on the last day of the quarter.

XML example:

<query>{7.IR.'this q'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'this+q'}

This example returns:

All records where the date or date/time in fid 7 occurs within the current quarter.

next q

Starts at 12:00:00 a.m. on the first day of the quarter after the current quarter ends, and continues for three months, ending at 11:59:59 on the last day of that quarter.

XML example:

<query>{7.IR.'next q'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'next+q'}

This example returns:

All records where the date or date/time in fid 7 occurs within the quarter following the current quarter.

last q

Starts at 12:00:00 a.m. on the first day of the quarter immediately preceding the current quarter and continues for three months, ending at 11:59:59 on the last day of the quarter.

XML example:

<query>{7.IR.'last q'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'last+q'}

This example returns:

All records where the date or date/time in fid 7 occurs within the quarter immediately preceding the current quarter.

next N q

 

Starts at 12:00:00 a.m. on first day of the quarter immediately following the current quarter and continues for N quarters. (The range does not include the current quarter.)

XML example:

<query>{7.IR.'next 5 q'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'next+5+q'}

This example returns:

All records where the date or date/time in fid 7 occurs within the 5 consecutive quarters starting the on the first day of next quarter.

last N q

Starts at 12:00:00 a.m. N quarters before the current quarter and continues for N quarters. (The range does not include the current quarter.)

XML example:

<query>{7.IR.'last 5 q'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'last+5+q'}

This example returns:

All records where the date or date/time in fid 7 falls within the 5 consecutive quarters that end immediately before the current quarter begins.

Relative date ranges: Fiscal Quarters

Fiscal year definitions vary from organization to organization. For each Quickbase application, you can define the fiscal year your company uses. You define the starting month of your fiscal year; the fiscal year continues for 12 months following the starting month.  (Start and end times are always based on the application's time zone.)

Quickbase automatically calculates your fiscal quarters based on the fiscal year starting month.

If your application manager does not set the fiscal year, Quickbase uses the calendar year as the default.

See the Quickbase online help to learn how to define the fiscal year for the application.

Operator Description

this fq

Starts at 12:00:00 a.m. on the first day of the current fiscal quarter and continues for three months, ending at 11:59:59 on the last day of the third fiscal quarter.

XML example:

<query>{7.IR.'this fq'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'this+fq'}

This example returns:

All records where the date in fid 7 occurs within the current fiscal quarter.

next fq

Starts at 12:00:00 a.m. on the first day of the fiscal quarter after the current quarter ends, and continues for three months, ending at 11:59:59 on the last day of the fiscal quarter.

XML example:

<query>{7.IR.'next fq'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'next+fq'}

This example returns:

All records where the date or date/time in fid 7 occurs within the fiscal quarter following the current fiscal quarter.

last fq

Starts at 12:00:00 a.m. on the first day of the fiscal quarter immediately preceding the current fiscal quarter and continues for three months, ending at 11:59:59 on the last day of the fiscal quarter.

XML example:

<query>{7.IR.'last fq'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'last+fq'}

This example returns:

All records where the date or date/time in fid 7 occurs within the fiscal quarter immediately preceding the current fiscal quarter.

next N fq

 

Starts at 12:00:00 a.m. on first day of next fiscal quarter and continues for N fiscal quarters. (The range does not include the current fiscal quarter.)

XML example:

<query>{7.IR.'next 5 fq'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'next+5+fq'}

This example returns:

All records where the date or date/time in fid 7 occurs within the 5 consecutive fiscal quarters starting the on the first day of next fiscal quarter.

last N fq

Starts at 12:00:00 a.m. N quarters before the current fiscal quarter and continues for N fiscal quarters. (The range does not include the current fiscal quarter.)

XML example:

<query>{7.IR.'last 5 fq'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'last+5+fq'}

This example returns:

All records where the date or date/time in fid 7 falls within the 5 consecutive fiscal quarters that end immediately before the current fiscal quarter begins.

Relative date ranges: Years

A year is the 12-month time period beginning at 12:00:00 a.m. on January 1st and ending at 11:59:59 on December 31st.  (Start and end times are always based on the application's time zone.)

Operator Description

this y

Starts at 12:00:00 a.m. January 1st of the current year and continues for 12 months (through December 31.)

XML example:

<query>{7.IR.'this y'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'this+y'}

This example returns:

All records where the date or date/time in fid 7 occurs within the current year.

next y

Starts at 12:00:00 a.m. on the first day of the year immediately following the current year and continues for 12 months (through 11:59:59 p.m. on December 31).

XML example:

<query>{7.IR.'next y'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'next+y'}

This example returns:

All records where the date or date/time in fid 7 occurs within the year following the current year.

last y

Starts at 12:00:00 a.m. on January 1st of the year immediately preceding the current year and continues for 12 months (through 11:59:59 p.m. on December 31.)

XML example:

<query>{7.IR.'last y'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'last+y'}

This example returns:

All records where the date or date/time in fid 7 occurs within the year immediately preceding the current year.

next N y

 

Starts at 12:00:00 a.m. on January 1st of the year immediately following the current year and continues for N years. (The range does not include the current year.)

XML example:

<query>{7.IR.'next 5 y'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'next+5+y'}

This example returns:

All records where the date or date/time in fid 7 occurs within the 5 consecutive years starting the on the first day of the year following the current year.

last N y

Starts at 12:00:00 a.m. N years before the current year and continues for N years. (The range does not include the current year.)

XML example:

<query>{7.IR.'last 5 y'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'last+5+y'}

This example returns:

All records where the date or date/time in fid 7 falls within the 5 consecutive years that end immediately before the current year begins.

Relative date ranges: Fiscal Years

Fiscal year definitions vary from organization to organization. For each Quickbase application, the application manager can define the fiscal year your company uses.  You define the starting month of your fiscal year; the fiscal year continues for 12 months following the starting month. The fiscal year ends at 11:59:59 on the last day of the 12th month.  (Start and end times are always based on the application's time zone.)

Quickbase automatically  calculates your fiscal quarters based on the fiscal year starting month.

If your application manager does not set the fiscal year, Quickbase uses the calendar year as the default.

Operator Description

this fy

Starts at 12:00:00 a.m. on the first day of the current year and continues for 12 months.

XML example:

<query>{7.IR.'this fy'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'this+fy'}

This example returns:

All records where the date or date/time in fid 7 occurs within the current fiscal year.

next fy

Starts at 12:00:00 a.m. on the first day of the fiscal year immediately following the current fiscal year and continues for 12 months.

XML example:

<query>{7.IR.'next fy'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'next+fy'}

This example returns:

All records where the date or date/time in fid 7 occurs within the fiscal year following the current fiscal year.

last fy

Starts at 12:00:00 a.m. on the first day of the fiscal year that immediately precedes g the current fiscal year and continues for 12 months.

XML example:

<query>{7.IR.'last fy'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'last+fy'}

This example returns:

All records where the date or date/time in fid 7 occurs within the fiscal year immediately preceding the current fiscal year.

next N fy

 

Starts at 12:00:00 a.m. on the start date of next fiscal year and continues for N years. (The range does not include the current fiscal year.)

XML example:

<query>{7.IR.'next 5 fy'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'next+5+fy'}

This example returns:

All records where the date or date/time in fid 7 occurs within the 5 consecutive fiscal years starting the on the first day of next fiscal year.

last N fy

Starts at 12:00:00 a.m. N fiscal years before the current fiscal year and continues for N fiscal years. (The range does not include the current fiscal year.)

XML example:

<query>{7.IR.'last 5 fy'}</query>

URL example:

https://target_domain/db/target_dbid?a=API_DoQuery&query={7.IR.'last+5+fy'}

This example returns:

All records where the date or date/time in fid 7 falls within the 5 consecutive fiscal years that end immediately before the current fiscal year begins.

Related articles

Top