Date Field Odds and Ends

Thirty years ago we stored dates and times in alphanumeric and numeric fields, because we didn’t have date, time, and timestamp data types. Many shops still store dates in alpha and numeric fields, some of them because they still run applications that date back to those dates, others because they’ve never bothered to learn to use the new data types. Here are three tips for working with date fields. I hope you enjoy them. Feel free to add to the discussion.

Hey, Ted:

In the old days, when dates were stored in numeric or alpha fields, we used invalid date values to indicate special conditions. A ship date of all 5s, for example, might mean that an order was on hold and could not be shipped. A ship date of zeros might mean that an order had not yet been shipped. But database fields that are defined with the date/time/timestamp data types cannot contain invalid values. How do you handle special values in such fields?

–Bob

I’ve seen this done two ways. The way I don’t like is to use a valid value that you would never use otherwise. For example, most factories in the United States are closed on December 25, so you could use that date to indicate that an order is on hold. I do not like that method because it requires counterintuitive logic in programs and queries.

select;
    when ShipDate = d'9999-12-31';
       // process not shipped 
    when %subdt(ShipDate:*months) = 12
     and %subdt(ShipDate:*days)   = 25;
       // process on-hold
    other;
       // process shipped order
endsl;

The other method of which I am aware is to use status fields in conjunction with date fields. That is, you would have a shipping date field and a shipping status field. The shipping status field might have values of blank (not shipped), H (on hold), and S (shipped). The logic is no more complicated than with the first method, and I think it can be easier to read and understand. The downside is that you have to update two fields, not one, when modifying a date.

D NotShipped      c                   const(' ')
D OnHold          c                   const('H')
D Shipped         c                   const('S')

 /free
     *inlr = *on;
     select;
         when ShipStatus = NotShipped;
            // process not shipped
         when ShipStatus = OnHold;
            // process on-hold
         other;
            // process shipped order
     endsl;

Hey, Ted:

Just because a date is a valid one does not mean it is reasonable. We had an example of this recently. Someone transposed two digits of a date when keying. Had someone else not caught the error, the customer’s order would have been delayed several years!

–Ron

I know this seems like common sense and we shouldn’t have to say it, but I have seen a lot of software that accepts any valid date, even dates far back in the past or far into the future. (I’ve also seen software that allows any date, even invalid ones.) Thanks for the reminder.

Hey, Ted:

Please explain DATFMT. I have never used it and seem to be getting by quite well without it.

–Ben

A date is a date is a date–almost. Dates of formats with two-digit years are limited to the range 1940 to 2039. But the system will let you compare an ISO date to a MDY date, for instance, or a EUR date to a Julian date.

DATFMT is used in DDS for physical and logical files to indicate the format of the date. Practically speaking, the only benefit I’ve ever seen to using this keyword is for the people who use Query. If your people are accustomed to viewing dates in six-digit month-day-year format, for example, you can specify DATFMT(*MDY), along with DATSEP(‘/’), and people using Query will see the dates in that format. This is consistent with the philosophy of database management systems, which says that users should be able to view data in the format of their choice, regardless of how the data is stored in the database.

In physical files, DATFMT is only allowed for the date data type, but in logical fields you can also use DATFMT for character, zoned decimal, and packed decimal data that redefines date fields in the physical file.

In RPG programs, you can use DATFMT in H and D specs. In H specs, DATFMT specifies the internal format for date literals, and default internal format for date fields. The default format is *ISO. You may override the format for date variables by using DATFMT in the D specs.

As long as you’re willing to work with dates in ISO format, you have no need of DATFMT. The important thing is that you’re using the date/time/timestamp data types, not numeric and character fields and variables.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot