Most of the frequently used "date variables" are pre-delivered as part of the variable functionality. Click here to see how to use date variables as part of queries.
Oftentimes, there may be a need to use these variables with offset – e.g. there is an existing variable for “Start date of current month”, but if your goal is to get the value “Last date of previous month”, you can use function ADD_DAYS_TO_DATE by wrapping around the current date variable.
Follow these steps to create the above example:
- From the query builder, select the connection profile and input the required SQL query. Click the “$$” icon from the toolbar and select the date variable for “Start date of current month” in the required format.
2. Click OK to place the variable in the query text area as follows and run the query to see the filtered resultset for "start date of the current month".
3. Now click "$$" again, switch to the tab "Predefined functions". Select ADD_DAYS_TO_DATE function. This function allows add or subtract X number of days from a given date. If you need to subtract, just give a negative signed value.
4. Click OK to place the select function in the query text area as follows. Replace the function placeholder text with the required values as shown in the below image and run the query to see the filtered resultset for "Last day of the prior month".