Filter Options for Date Fields
When you define a query and include date fields as filters, you select criteria operators Fields that allow you to define the specific conditions a record must meet to be included in a group. such as "Is," "Before," or "After," and then the date filter options to use. For example, to create a query of records added the previous week, you select Date added "Is" "Last week."
The "Rolling date" option is a date range relative to the current date, that either occurred in the past or will occur in the future. For example, you can create a query to search for constituent records that were added three days ago or memberships that will expire three months from today. When you select "Rolling date," specify the number and type of range to use and then select "Ago" or "From today."
Note: The "Rolling date" option is only available when you use a source view from the data warehouse, so the results include dates relative to the last time the data was refreshed. Also, rolling dates are not available for "fuzzy" or incomplete dates such as 08/2014.
The tables below provide detail about each date filter option that is available. For "Rolling date," the tables list each combination of options you can select.
Note: The tables represent the number for each rolling date combination as N (such as N Day(s) Ago).
Days
A day starts at 12:00:00 a.m. and continues until 11:59:59 p.m. Start and end times use the database server's time zone.
|
Option |
Description |
| Specific date | Starts at 12:00:00 a.m. on the day you specify and continues for 24 hours. |
| Today | Starts at 12:00:00 a.m. on the current day and continues for 24 hours. |
| Yesterday | Starts at 12:00:00 a.m. on the day before the current day and continues for 24 hours. |
| Rolling date: N Day(s) Ago |
Starts at 12:00:00 a.m. n days before the current day and continues 24 hours. For example, if the current day is August 8th, three days ago is August 5th.
|
| Tomorrow | Starts at 12:00:00 a.m. on the day after the current day and continues for 24 hours. |
| Rolling date: N Day(s) From today |
Starts at 12:00:00 a.m. n days from the current day and continues for 24 hours. For example, if the current day is August 8th, three days from today is August 11th.
|
Weeks
A week starts at 12:00:00 a.m. on Sunday and continues for seven consecutive days until 11:59:59 p.m. on the seventh day. Start and end times use the database server's time zone.
|
Option |
Description |
| This week | Starts at 12:00:00 a.m. on the Sunday of the current week and continues for seven days. |
|
Last week |
Starts at 12:00:00 a.m. on the Sunday of the week before the current week and continues for seven days. |
| Rolling date: N Week(s) Ago |
Starts at 12:00:00 a.m. on the Sunday of the week n weeks before the current day and continues for seven days. For example, if today is Saturday, August 14th, one week ago is Sunday, August 1st to Saturday, August 7th.
|
| Next week | Starts at 12:00:00 a.m. on the Sunday of the week after the current week and continues for seven days. |
| Rolling date: N Week(s) From today |
Starts at 12:00:00 a.m. on the Sunday of the week n weeks from the current day and continues for seven days. For example, if today is Monday, August 2nd, one week from today is Sunday, August 8th to Saturday, August 14th.
|
Months
A month starts at 12:00:00 a.m. on the first day of a month and continues until 11:59:59 p.m. on the last day of the month. Start and end times use the database server's time zone.
|
Option |
Description |
| This month | Starts at 12:00:00 a.m. on the first day of the current month and continues for all the days in the month. |
| Last month | Starts at 12:00:00 a.m. on the first day of the month before the current month and continues for all the days in that month. |
| Rolling date: N Month(s) Ago |
Starts at 12:00:00 a.m. on the first day of the month that started n months before the current day and continues for all the days in that month. For example, if today is August 8th, three months ago is May 1st to May 31st.
|
| Next month | Starts at 12:00:00 a.m. on the first day of the month after the current month and continues for all the days in that month. |
| Month to date | Starts at 12:00:00 a.m. on the first day of the current month and continues to the current date. |
| Rolling date: N Month(s) From today |
Starts at 12:00:00 a.m. on the first day of the month n months from the current day and continues for all the days in that month. For example, if today is August 8th, three months from today is November 1st to November 30th.
|
Quarters
Each year is divided into four, three month long calendar quarters. The program uses the following quarter definitions:
-
First quarter - January through March
-
Second quarter - April through June
-
Third quarter - July through September
-
Fourth quarter - October through December
|
Option |
Description |
| This quarter | Starts at 12:00:00 a.m. on the first day of the current calendar quarter and continues to the end of the quarter. |
| Last quarter | Starts at 12:00:00 a.m. on the first day of the calendar quarter before the current calendar quarter and continues to the end of that quarter. |
| Rolling date: N Quarter(s) Ago |
Starts at 12:00:00 a.m. on the first day of the calendar quarter that started n quarters before the current day and continues to the end of that quarter. For example, if today is August 8th, one quarter ago is April 1st through June 30th.
|
| Next quarter | Starts at 12:00:00 a.m. on the first day of the calendar quarter after the current calendar quarter and continues to the end of that quarter. |
| Quarter to date | Starts at 12:00:00 a.m. on the first day of the current quarter and continues to the current date. |
| Rolling date: N Quarter(s) From today |
Starts at 12:00:00 a.m. on the first day of the calendar quarter n quarters from the current day and continues to the end of that quarter. For example, if today is August 8th, one quarter from today is October 1st through December 31st.
|
Years
A year is the 12 month time period that begins at 12:00:00 a.m. on January 1st and ends at 11:59:59 p.m. on December 31st.
|
Option |
Description |
| This calendar year | Starts at 12:00:00 a.m. on January 1st of the current year and continues for 365 days. |
| Last calendar year | Starts at 12:00:00 a.m. on January 1st of the year before the current year and continues for 365 days. |
| Rolling date: N Calendar year(s) Ago |
Starts at 12:00:00 a.m. on January 1st of the year that started n years before the current day and continues for 365 days. For example, if today is August 8, 2014, one year ago is January 1, 2013, through December 31, 2013.
|
| Next calendar year | Starts at 12:00:00 a.m. on January 1st of the year after the current year and continues for 365 days. |
| Calendar YTD | Starts at 12:00:00 a.m. on January 1st of the current year and continues to the current date. |
| Rolling date: N Calendar year(s) From today |
Starts at 12:00:00 a.m. on January 1st of the calendar year n years from the current day and continues to the end of that year. For example, if today is August 8, 2014, one year from today is January 1, 2015, through December 31, 2015.
|