NOMAD/UltraQuest Date Arithmetic and ADDATE Function Explained
In the last education post, we saw how the DATE data type in UltraQuest and NOMAD may be used to enter and display dates. In this post, we will look at how dates are manipulated.
The simplest means of manipulating a date is adding or subtracting a number from it. For example, suppose you have a database of library books that have been lent and want to see when they are due, 3 weeks from the day they were lent. Then the desired expression is:
BOOKS.LENT_DATE + 21
To add such a column to Reporter, go to the Columns dialog, click on Add (x*y)+2 to open the Expression Column dialog, select the column (e.g., LENT_DATE), click on the Insert button, and then type or insert +7.
The ADDATE and GROUPDATE functions are especially useful in manipulating dates. ADDATE will be described here, and GROUPDATE in another post.
ADDATE may be used to add or subtract any number of months or years to a date. Its syntax is:
ADDATE(date-expression, months [,years])
For example, suppose you want to build a data filter to search a Human Resources database to find employees hired (DATE_HIRED) within the last year. In NOMAD, you may build the SELECT expression:
DATE_HIRED GT ADDATE(&&DATE,0,-1)
To build the same data filter in UltraQuest Reporter, go to the Data Filtering dialog in the Design desktop, select your date column (e.g., DATE_HIRED), click Add >, and click Build filter. In the Build Filter dialog, Select GT from the Operator drop-down list and click on Variable. In the Variables dialog, select &&DATE and click Continue. Back on the Build Filter dialog, where &&DATE is shown in a text field, type more text so it now reads "ADDATE(&&DATE,0,1)", and click Continue. You will have built the expression shown above.
NOMAD users may simply enter the above expression in a SELECT command or LIST...WHERE clause.
Please feel to contact UltraQuest and NOMAD support if you have any additional questions about date arithmetic or the ADDATE function.
More information about the ADDATE function may be found in the "Functions and Operators" section of Chapter 5 of the UltraQuest and NOMAD Reference Manual.
Next week's topic: GROUPDATE