Love it or hate it, Query/400 is one of the most widely used reporting tools for OS/400. One of the big challenges that can face a Query/400 user is dealing with date values. Fortunately, Query/400 includes a series of built-in functions specifically designed for dealing with dates.

The most valuable of these date functions is the CURRENT(DATE) function, which allows you to read the current system date into your query. If you've created a query that runs from a job scheduler on a regular basis and uses date ranges as part of its criteria, you'll understand how invaluable the ability to determine the current date is. Figure 1 shows an example of creating a field containing the current date from the "Define Results Field" option in Query/400.

Figure 1: Use Query/400's "Define Results Field" option to create a field containing the current date. (Click images to enlarge.)

In addition to the DATE option above, the CURRENT() function can also accept values of TIME to return the time, TIMESTAMP to return a timestamp value, and TIMEZONE to retrieve the current time zone.

The value returned by the example above will be in a DATE format, which can present a problem if your application stores dates in numeric fields, as many do. You can deal with this problem by using a series of Query/400 functions. The DATE() function will allow you to convert a string value that evaluates a date into a DATE format. This by itself still doesn't solve the problem if your application stores the date in yyyymmdd numeric format. To deal with that problem, you combine the DIGITS() function, which converts a numeric value to a character value, with the SUBSTR() function to extract portions of the field. The example in Figure 2 converts an 8-digit numeric date in yyyymmdd format to a DATE format.

Figure 2: Convert an 8-digit numeric date in yyyymmdd format to a DATE format.

If the value of ORSHDT above was 20030401, this example would first break the month, day, and year values out of that field and insert a slash character to result in a character value of 04/01/2003. The DATE() function then takes this value and converts it into DATE format. The resulting value in the field SHIPDT can be compared to the value of the CURDT field shown earlier. Using this example, you would be able to create a report that shows all orders that were scheduled to ship today or earlier from the Query/400 Select Records screen, as shown in Figure 3 below.

Figure 3: Using the Select Records screen, you can create a report.

Since both of your values are now in a date format, you can easily compare these values to each other. In many cases, however, the comparison that needs to be done is not based on the current date; it's based on some number of days before the current date. For example, to create a query that shows all orders entered into the system within the past seven days, you'd use the DAYS() function. This function converts a date value into a number-of-days value that can be used to perform mathematical operations on a date. The example in Figure 4 evaluates to a date seven days earlier than the current date.

Figure 4: Evaluate to a date seven days earlier than the current date.

This example also uses the DATE() function to convert the value back into DATE format.

You can also use the DAYS() function to determine the number of days between two dates. This can be used in the calculation of an elapsed-days value as would be used to determine number of days for payment. The example in Figure 5 calculates the number of days between two dates.

Figure 5: Calculate the number of days between two dates.

This example assumes that the fields PAYDT and DUEDT are fields that are in DATE format or have been converted to DATE format as shown earlier. The resulting value will represent the number of days difference between the payment date (PAYDT) and the due date (DUEDT).

Similarly, this function could be used to determine a due date based on a given invoice date and payment terms days. Figure 6 assumes 30-day payments terms.

Figure 5: Determine a due date based on invoice date and payment terms.

The resulting value would be a DATE field containing the date for the invoice date (INVDTE) plus 30 days.

In addition to functions that allow you to work with full dates and date increments, Query/400 also has functions that allow you to pull a date field apart. The MONTH(), DAY(), and YEAR() functions allow you to extract portions of the date field into a numeric value representing the month, day, or year, respectively. Using these functions, it's possible to take a DATE field and convert it into a numeric (yyyymmmdd) value. Figure 6 takes the DUEDT field created above and converts it into an 8-digit numeric value.

Figure 6: Convert the DUEDT field created above into an 8-digit numeric value.

The resulting value in yyyymmdd format can then be used for comparison to other numeric format date values, or you can store it in an output file for later use.

These functions can really help you extend the usability of Query/400. By using these date functions, you can automate daily queries rather than update the query every day. So take another look at Query/400's date functions and bring your query skills up to DATE.

Mike Faust is MIS Manager for The Lehigh Group in Macungie, Pennsylvania. Mike is also the author of The iSeries and AS/400 Programmer's Guide to Cool Things from MC Press. You can contact Mike at This email address is being protected from spambots. You need JavaScript enabled to view it..

Mike Faust is a senior consultant/analyst for Retail Technologies Corporation in Orlando, Florida. Mike is also the author of the books Active Server Pages Primer, The iSeries and AS/400 Programmer's Guide to Cool Things, JavaScript for the Business Developer, and SQL Built-in Functions and Stored Procedures. You can contact Mike at This email address is being protected from spambots. You need JavaScript enabled to view it..

---

MC Press books written by Mike Faust available now on the MC Press Bookstore.

		Active Server Pages Primer Learn how to make the most of ASP while creating a fully functional ASP "shopping cart" application. List Price $79.00 Now On Sale
		JavaScript for the Business Developer Learn how JavaScript can help you create dynamic business applications with Web browser interfaces. List Price $44.95 Now On Sale
		SQL Built-in Functions and Stored Procedures Unleash the full power of SQL with these highly useful tools. List Price $49.95 Now On Sale