One of the most common issues in SQL is dealing with dates. This article will introduce you to the joys of dates, both legacy and date data type.

With the introduction of the date data type (and its siblings, the time and timestamp data types), the i5/OS database took a huge step forward. And although RPG's acceptance was a little more grudging, we eventually got to where we are today. However, if you're like many shops, you may have to deal with both legacy dates and date-type dates. And when you're talking about user-controlled queries, you still have to get those date values from the user via fields on the screen.

Dates and the BIFs That Love Them

Before we go into the business of making a query that supports dates, let's do a quick rundown of a few lines of RPG that will reintroduce you to dates. Assume for the following that myMDY is a 6,0 numeric field, myDate is a date type field, and myISOAlpha is a 10-character alpha field. Execute the following code:

  myMDY = 093001;

  myDate = %date( myMDY: *mdy);

  myISOAlpha = %char( myDate: *iso);

At this point, myISOAlpha will contain 2001-09-30. The date BIFs are very powerful, and they allow some very nice features, including the fact that once I have a valid value in myMDY, I can do the whole conversion in one step:

  myISOAlpha = %char( %date( myMDY: *mdy): *iso);

I love BIFs! I could easily spend an entire article on the date BIFs. In fact, if you'd like something like that, just let me know and perhaps I can do a more in-depth article on date handling in RPG. For now, though, I just wanted to get them into the picture.

Next, let's outline the business issue.

Prompting for a Date Range

Since these are relatively short articles, I'm going to skip directly to the end of the chapter and show you how to handle dynamic date-range selection; whether you're selecting a single date or a list of dates, the same concepts apply.

First, I'll show you how to handle a legacy date field; date-type dates require only minor tweaking. For the purposes of this example, I'll use one of the most common legacy date formats: an eight-digit numeric CCYYMMDD field. Certainly, there are other viable legacy date formats; this technique will work for any of them as long as the date is naturally sortable (this includes Julian and seven-digit CYYMMDD types of dates). If your date field is not sortable (for example, any non-Y2K date with a two-digit year or one in which the day and month precede the year), then you'll need additional work that's outside the purview of today's article. The good news is that we've gotten rid of most of those fields (right?).

Prompting for a date field involves two components: letting the user enter the range (and editing it) and then including the date range in the query. Prompting is actually a little more complex than you might think. Several questions come into play. Here are just a couple:

1. Are your fields numeric or alpha?
2. Do you require four-digit years?

The answers aren't always obvious, and they're business decisions, not architectural ones. For example, alpha fields are sometimes easier for users because they can see the slashes. Of course, you can do some of that using edit codes and even edit masks, and I don't want to go too far off track discussing the pros and cons of various UI techniques. No matter what your display format, though, you'll need to deal with one specific issue: that of special low and high values.

In any date range, you'll see something like this:

SELECT DATES: 000000 TO 999999

There are variations on this. For example, the beginning, end, or both may be defaulted to today's date. Or they may reflect the first and last days of the current month. But many user interfaces are like the one above, which makes it easier for the user to select the entire date range by simply putting zeros in the From Date and all nines in the To Date. Such programs will often default the from and to dates to zeros and nines, respectively.

It only takes a little extra code to handle these special values. Let's assume that the fields on the screen are XXFRDATE and XXTODATE. Handling the testing and conversion goes a little like this:

d fromDate        s              6s 0

 (...)

 if XXFRDATE = *zeros;

   fromDate = *zeros;

 else;

   test(de) XXFRDATE;

   if %error;

     // ... do error stuff ...

   else;

     fromDate = %dec( %date( XXFRDATE: *mdy): *iso);

   endif;

 endif;

The test(de) operation code is a little bit of a throwback; rather than return an error, the BIF actually sets the run-time error indicator, which you then test with the %error BIF. It is actually important that you do this, because if you pass bad dates to the embedded SQL, the SQL will fail miserably. To finish this snippet, you'll need to insert your own error-handling, typically highlighting the field in error. Then write a similar piece of code for XXTODATE, replacing *zeros with *all'9' or *hival. Now you've got fromDate and toDate with the appropriate values. The SQL is quite simple after that:

  exec SQL declare Orders cursor for

    select OHORD from ORDHDR

      where OHDATE between :fromDate and :todate;

As is often the case with SQL, once you've done the proper prior preparation, the execution is almost anticlimactic. A simple where/between is all that is required to insert the user-selectable date range into your SQL query.

Handling Date Data Types

The previous code was legacy code. Handling date-type data fields in the database requires very little change. In that case, define your fromDate work field as a date-type field:

d fromDate        s               d

Then, instead of setting fromDate to *zeros, set it to *loval.

  fromDate = *loval;

The same sort of translation applies to the toDate field; replace *all'9' with *hival. Of course, if you chose *hival in the first place, you wouldn't even have to make that change. Even more fun, the SQL query doesn't change at all. The where/between syntax works just as well for date data types as it does for legacy dates.

One big caveat exists for this technique, however: you'll need to be sure that your program's SQL date format (set during the compile) is set to an eight-digit type (either *ISO or *USA). If you have a six-digit date format such as *MDY (the default on many systems), then you'll receive an RNX0114 error ("The year portion of a Date or Timestamp value is not in the correct range."). You'll need to go back and recompile the program using the appropriate compiler option. I usually use DATFMT(*ISO) on the CRTSQLRPGI command. You can also use the SET OPTION statement in SQL to set your job's current date format.

That's it for date ranges. As you might guess, you can also apply this same concept to single dates or lists of dates. Enjoy!