Advanced Date Queries
The Query functionality has the ability to parse date values using the system date, allowing users to specify an offset value from a particular field as well as other derived values such as TODAY, NOW, MONDAY etc. This functionality allows the creation of self-updating queries and reports.
Date Parsing Syntax
The following syntax is available when performing advanced date queries:
|
Point In Time |
|
|---|---|
| NOW | LAST_DAY_OF_WEEK |
| TODAY | LAST_SECOND_OF_WEEK |
| MONDAY | FIRST_DAY_OF_MONTH |
| TUESDAY | LAST_DAY_OF_MONTH |
| WEDNESDAY | LAST_SECOND_OF_MONTH |
| THURSDAY | FIRST_DAY_OF_QUARTER |
| FRIDAY | LAST_DAY_OF_QUARTER |
| SATURDAY | FIRST_DAY_OF_YEAR |
| SUNDAY | LAST_DAY_OF_YEAR |
| FIRST_SECOND_OF_DAY | LAST_SECOND_OF_YEAR |
| LAST_SECOND_OF_DAY | FIRST_DAY_OF_FISCAL_YEAR |
| FIRST_MINUTE_OF_DAY | LAST_DAY_OF_FISCAL_YEAR |
| LAST_MINUTE_OF_DAY | LAST_SECOND_OF_FISCAL_YEAR |
| LAST_SECOND_OF_DAY | FIRST_DAY_OF_FISCAL_HALF_YEAR |
| FIRST_DAY_OF_WEEK | LAST_DAY_OF_FISCAL_HALF_YEAR |
Note: TODAY query uses the current system date and 00:00:00 as time, while NOW query uses the current system date and time.
Tip: YESTERDAY can be derived by using the query TODAY-1.
|
Differentials |
|---|
| SECONDS |
| MINUTES |
| HOURS |
| DAYS |
| WEEKS |
| MONTHS |
| YEARS |
Examples
| If | Then |
|---|---|
| If TODAY=Thu Jul 09 00:00:00 BST 2017 | Then TODAY+1HOURS=Thu Jul 09 01:00:00 BST 2017 |
| If NOW=Thu Jul 09 10:24:20 BST 2017 | Then NOW+1WEEKS=Thu Jul 16 10:24:20 BST 2017 |
| If MONDAY=Mon Jul 06 00:00:00 BST 2017 | Then MONDAY+1DAYS=Tue Jul 07 00:00:00 BST 2017 |
| If LAST_SECOND_OF_DAY=Thu Jul 09 23:59:59 BST 2017 | Then LAST_SECOND_OF_DAY+1HOURS=Fri Jul 10 00:59:59 BST 2017 |
| If FIRST_DAY_OF_MONTH=Wed Jul 01 00:00:00 BST 2017 | Then FIRST_DAY_OF_MONTH+1WEEKS=Wed Jul 08 00:00:00 BST 2017 |
| If LAST_DAY_OF_YEAR=Thu Dec 31 00:00:00 GMT 2017 | Then LAST_DAY_OF_YEAR-12WEEKS=Thu Oct 08 00:00:00 BST 2017 |
| If TRANSACTION DATE=Thu Nov 30 00:00:00 GMT 2017 | Then MONDAY(Transaction_Date)-7DAYS=Thu Nov 23 00:00:00 GMT 2017 |
| If TRANSACTION DATE=Thu Nov 30 00:00:00 GMT 2017 | Then +(Transaction Date):["FIRST_DAY_OF_QUARTER" TO "LAST_DAY_OF_QUARTER"] will return all dates between 01/10/2017 and 31/12/2017. |