Date and Date/Time Operators

Share
Dark
Light

Date and Date/Time Operators

Share
Dark
Light

Article summary

Did you find this summary helpful?

Thank you for your feedback

Date operators allow for the comparison of dates to specific points in time or time ranges, helping to analyze and interpret date-based data. They enable you to determine if a date is "on" a particular day, "before" a certain deadline, or "after" a given time frame. For instance, you can check if a due date falls "between" two dates or occurs "before" next week. Additionally, relative operators offer flexibility by using periods like "last 30 days" or "next quarter," making it easier to monitor and evaluate trends in date-related information.

Note
The operators mentioned here apply to both date and date-time fields. However, for the date-time fields, the system considers only the date and not the time.

This section explains:

Operators with Static dates and Rolling time frames
Trend Operators
Relative Operators
Comparison Operators

Operators with Static Dates and Rolling Time Frames

The operators provide options for defining periods and contextual settings to refine results based on specific conditions. You can choose between static dates and rolling time frames, depending on what you need. For fixed points in time, the static option with a date picker is ideal. However, for more dynamic ranges like "Last 7 Days" or "Next Month," rolling time options from the dropdown menu are more suitable.

The following table describes date operators that require additional criteria to define their functionality. The Details column provides examples and references.

Operator	Description	Details
on	Filters records where a specific date matches exactly with a specified date. This value can either be a static date, selected using a date picker or a rolling period, which dynamically adjusts based on the current date (Example: "Today" or "Next Monday"). This operator is useful to specify exact dates within both fixed and relative time frames.	Static date: Due date is on January 15, 2025. Rolling period: Due date is on Today. See Rolling period options.
not on	Filters records where a specific date does not match the specified date. The value can be either a static date chosen with a date picker or a rolling period that adjusts dynamically based on the current date (Example: "Today" or "Next Monday"). This operator helps exclude records tied to a particular date within both fixed and relative time frames.	Static date: Due date is not on January 15, 2025. Rolling period: Due date is not on Today. See Rolling period options.
before	If you choose a specific date (static value), it checks if the date is before a specified date. If you choose a specific rolling time period option, it continuously checks if the date is before a dynamically changing point in time, relative to the current date.	Static date: Due date is not on December 20, 2025. Rolling period: Due date is before last week. See Rolling period options.
at or before	Filters records where a specific date falls before the specified date. The value can be either a static date, selected using a date picker, or a rolling period that adjusts dynamically based on the current date (Example: "Last Week" or "Yesterday"). This operator is useful to identify records with dates before a specified point in both fixed and relative time frames.	Static date: Due date is before January 15, 2025. Rolling period: Due date is before Today. See Rolling period options.
after	If you choose a specific date (static value), it checks if the date is later than the specified date. If you choose a specific rolling period option, it continuously checks if the date is after a dynamically changing point in time, relative to the current date. This operator is useful to identify records with dates after a specified point in both fixed and relative time frames.	Static date: Due date is after February 20, 2025. Rolling period: Due date is after this quarter. See Rolling period options.
at or after	If you choose a specific date (static value), it checks if the date is later than the specified date. If you choose a specific rolling period option, it continuously checks if the date is at or after a dynamically changing point in time, relative to the current date. This operator is useful to identify records with dates that meet or exceed a specified point in both fixed and relative time frames.	Static date: Due date is at or after March 10, 2025. Rolling period: Due date is after this year. See Rolling period options.
between	Checks if the date is between two specified dates. If you choose a specific rolling time period option, it continuously checks if the date is within the range of two dynamically changing points in time, relative to the current date. This operator is useful to identify records with dates that fall within a specified range in both fixed and relative time frames.	Static date: Due date is between April 1, 2025, and April 30, 2025. Rolling period: Due date is between this week and next week.

Operator

Description

Details

Filters records where a specific date matches exactly with a specified date.

This value can either be a static date, selected using a date picker or a rolling period, which dynamically adjusts based on the current date (Example: "Today" or "Next Monday"). This operator is useful to specify exact dates within both fixed and relative time frames.

Static date: Due date is on January 15, 2025.

Rolling period: Due date is on Today.

See Rolling period options.

not on

Filters records where a specific date does not match the specified date.

The value can be either a static date chosen with a date picker or a rolling period that adjusts dynamically based on the current date (Example: "Today" or "Next Monday").

This operator helps exclude records tied to a particular date within both fixed and relative time frames.

Static date: Due date is not on January 15, 2025.

Rolling period: Due date is not on Today.

See Rolling period options.

before

If you choose a specific date (static value), it checks if the date is before a specified date.

If you choose a specific rolling time period option, it continuously checks if the date is before a dynamically changing point in time, relative to the current date.

Static date: Due date is not on December 20, 2025.

Rolling period: Due date is before last week.

See Rolling period options.

at or before

Filters records where a specific date falls before the specified date.

The value can be either a static date, selected using a date picker, or a rolling period that adjusts dynamically based on the current date (Example: "Last Week" or "Yesterday").

This operator is useful to identify records with dates before a specified point in both fixed and relative time frames.

Static date: Due date is before January 15, 2025.

Rolling period: Due date is before Today.

See Rolling period options.

after

If you choose a specific date (static value), it checks if the date is later than the specified date.

If you choose a specific rolling period option, it continuously checks if the date is after a dynamically changing point in time, relative to the current date. This operator is useful to identify records with dates after a specified point in both fixed and relative time frames.

Static date: Due date is after February 20, 2025.

Rolling period: Due date is after this quarter.

See Rolling period options.

at or after

If you choose a specific date (static value), it checks if the date is later than the specified date.

If you choose a specific rolling period option, it continuously checks if the date is at or after a dynamically changing point in time, relative to the current date. This operator is useful to identify records with dates that meet or exceed a specified point in both fixed and relative time frames.

Static date: Due date is at or after March 10, 2025.

Rolling period: Due date is after this year.

See Rolling period options.

between

Checks if the date is between two specified dates.

If you choose a specific rolling time period option, it continuously checks if the date is within the range of two dynamically changing points in time, relative to the current date. This operator is useful to identify records with dates that fall within a specified range in both fixed and relative time frames.

Static date: Due date is between April 1, 2025, and April 30, 2025.

Rolling period: Due date is between this week and next week.

Rolling Period Options and Examples

The following table explains the rolling period options and examples for these operators assuming that the current date is 1 January 2024.

On
not on
before
at or before,
after
at or after
between

Rolling Period	Description
Today	Matches records with today's date (Example: "January 1, 2024").
Yesterday	Matches records with yesterday's date (Example: "December 31, 2023").
Tomorrow	Matches records with tomorrow's date (Example: "January 2, 2024").
This week	Matches records within the current week (from Monday, "January 1, 2024" to Sunday, "January 7, 2024").
Last week	Matches records from the previous week (from Monday, "December 25, 2023" to Sunday, "December 31, 2023").
Next week	Matches records within the upcoming week (from Monday, "January 8, 2024" to Sunday, "January 14, 2024").
This month	Matches records within the current month (Example: "January 1, 2024" to "January 31, 2024").
Last month	Matches records from the previous month (Example: "December 1, 2023" to "December 31, 2023").
Next month	Matches records within the upcoming month (Example: "February 1, 2024" to "February 29, 2024").
Last 3 months	Matches records from the last 3 months (Example: "October 1, 2023" to "December 31, 2023").
Last 6 months	Matches records from the last 6 months (Example: "July 1, 2023" to "December 31, 2023")
Last 9 months	Matches records from the last 6 months (Example: "April 1, 2023" to "December 31, 2023")
Last 12 months	Matches records from the last 6 months (Example: "January 1, 2023" to "December 31, 2023")
This quarter	Matches records within the current quarter (Example: "January 1, 2024" to "March 31, 2024").
Last quarter	Matches records from the previous quarter (Example: "October 1, 2023" to "December 31, 2023").
Last 2 quarters	Matches records from the previous quarter (Example: "July 1, 2023" to "December 31, 2023").
Next quarter	Matches records within the upcoming quarter (Example: "April 1, 2024" to "June 30, 2024").
Next 2 quarters	Matches records within the upcoming quarter (Example: "April 1, 2024" to "September 30, 2024").
Last 1 day	Matches records from the last 24 hours (Example: from "December 31, 2023" to "January 1, 2024").
Last 7 days	Matches records from the last 7 days (Example: "December 25, 2023" to "January 1, 2024").
Last 10 days	Matches records from the last 10 days (Example: "December 22, 2023" to "January 1, 2024").
Last 1 week	Matches records from the last calendar week (Example: Monday, "December 25, 2023" to Sunday, "December 31, 2023").
Last 2 weeks	Matches records from the last 2 weeks (Example: "December 18, 2023" to "December 31, 2023").
Last 3 weeks	Matches records from the last 3 weeks (Example: "December 11, 2023" to "December 31, 2023").
Last 1 month	Matches records from the last calendar month (Example: "December 1, 2023" to "December 31, 2023").
Last 6 months	Matches records from the last 6 months (Example: "July 1, 2023" to "December 31, 2023").
This year	Matches records within the current calendar year (Example: "January 1, 2024" to "December 31, 2024").
Next year	Matches records within the upcoming calendar year (Example: "January 1, 2025" to "December 31, 2025").
Last year	Matches records from the previous calendar year (Example: "January 1, 2023" to "December 31, 2023").
Last 2 years	Matches records from the last 2 calendar years (Example: "January 1, 2022" to "December 31, 2023").
Last 7 days	Matches records from the last 7 days (Example: "December 25, 2023" to "January 1, 2024").
Last 30 days	Matches records from the last 30 days (Example: "December 2, 2023" to "January 1, 2024").
Last 60 days	Matches records from the last 60 days (Example: "November 2, 2023" to "January 1, 2024").
Last 90 days	Matches records from the last 90 days (Example: "September 2, 2023" to "January 1, 2024").
Last 120 days	Matches records from the last 120 days (Example: "May 3, 2023" to "January 1, 2024")

Was this article helpful?

What's Next

Trend Operators

Table of contents