(Available in ExoInsight v10 and above)
ExoInsight provides the ability to create dynamic date references based on the system date of the ExoInsight server. When using an ExoDate reference within a query, the ExoDate will resolve to the current date/time at the time the query is run. You can customize the ExoDate reference for whatever date format is needed. You can also references dates in the past and future based on the ExoInsight server date. This allows you complete control over how dynamic dates are used within your ExoInsight queries.
ExoDate Formats
ExoDates can be customized to return the date/time in any format required. Use the following symbols in your format pattern:
| y | Year (e.g. 12 or 2012). Use either yy or yyyy (upper-case should only be used to represent the year that the full week falls in, which could be different than the year the day falls in) |
| M | Month in year. Number of M's determine length of format (e.g. MM, MMM or MMMM) |
| d | Day in month. Number of d's determine length of format (e.g. d or dd) |
| h | Hour of day, 1-12 (AM / PM) (normally hh) |
| H | Hour of day, 0-23 (normally HH) |
| m | Minute in hour, 0-59 (normally mm) |
| s | Second in minute, 0-59 (normally ss) |
| S | Millisecond in second, 0-999 (normally SSS) |
| E | Day in week (e.g Monday, Tuesday etc.). Use E for three letter abbreviation, or EEEE for full name. |
| D | Day in year (1-366) |
| F | Day of week in month (e.g. 1st Thursday of December) |
| w | Week in year (1-53) |
| W | Week in month (0-5) |
| a | AM / PM marker |
| k | Hour in day (1-24, unlike HH's 0-23) |
| K | Hour in day, AM / PM (0-11) |
| z | Time Zone |
Combine any of the symbols above to create your ExoDate in whatever format you need. Some common examples are below (all assume that the date on the ExoInsight server is April 10, 2022).
dd-MMM-yyyy = 10-APR-2022
yy/MM/dd = 22/04/10
mm-yy = 04-22
MMM-yy= Apr-22
MMMM dd, yyyy = April 10, 2022
Static Numeric Values
You can use any numeric character in the ExoDate pattern, which is very useful if you like to always return the first value of a month or year, or other specific number within your ExoDate. For example, to return the first date of the month regardless of when the query was run, you could set the ExoDate value to:
01-MMM-yyyy = 01-APR-2022
Because the "01" is not a reserved character it simply returns the same value and the MMM and yyyy values will be dynamically determined based on the ExoInsight server date.
ExoDate Offsets
Sometimes you need to pull back a date before or after the ExoInsight server date. Using the ExoInsight server date as an anchor, you can pull back any number of years, months, days, weeks, etc either before or after. To do this you would add the offset anchor to the ExoDate syntax:
<ExoDate pattern>;<offset clause>
The offset clause consists of a date section name and a + or - and number of units to add or subtract. For example, if you wanted to pull a date one month prior to the current month, you could use this syntax:
MMM;Month-1
If run on April 10, 2022, the ExoDate above would return "Mar". To return one year and one month prior, you could use the following:
MM-yyyy;Year-1,Month-1
The ExoDate above would return "03-2021" if run on April 10, 2022.
ExoDate Tester
ExoInsight provides a feature that allows you to test your date pattern to make sure it aligns with your expectations. Log into ExoInsight and navigate to the ExoInsight Variables screen. There you will see a button called Test ExoDate variables:
Clicking this button will bring up a dialog box. Enter any date pattern into the ExoDate field, then click the Check ExoDate button. You will then be able to see exactly how the ExoDate will be used in the query, based on the current date on the ExoInsight server:
Creating ExoDate ExoVars
A common usage of ExoDates is to create ExoInsight Variables that reference them. The ExoInsight Variables can then be referenced in queries like any other ExoInsight Variable, except they will dynamically resolve to whatever the ExoDate pattern returns at run time. The format of the "Variable Value" field when creating an ExoInsight Variable that references an ExoDate is as follows:
ExoDate=<ExoDate pattern>
For example, to create an ExoInsight Variable that always returned the current month and year at run time, the Variable Value would be:
ExoDate=MMM-yyyy
Referencing ExoDates Directly In Queries
You can reference ExoDates directly in queries without creating an ExoInsight Variable first. To do so, simply use the regular ExoVar syntax with the ExoDate variable syntax:
[ExoVar=ExoDate=<ExoDate pattern>]
For example, to use an ExoDate in your query that references the last day of the current year, you can use:
[ExoVar=ExoDate=yyyy-12-31]