Everything You Ever Wanted to Know About Converting Dates

by Bruce Guetzkow

The code for this article is available for download.

Although the date data type field has been around for several years now, many of us still have dates stored in our database as numeric or alpha fields. We've all used various ways to convert those dates to other formats so they are more human-readable or to comply with client or vendor needs: data structures, move left and move right, the infamous multiplication with truncation trick. While we may not use the date data type in the database, we can still use the date data type to simplify converting dates.

Start with a Date

The first step in simplifying date conversions is to convert your date to a date data type. Fortunately, IBM, has made that a one-step procedure in most cases: the %DATE built-in-function (BIF). If your dates come in one of the 12 standard formats all you need to do is indicate the field containing your date and the format it is in and the %DATE BIF does the rest. You can read about the 12 standard formats in the iSeries WebSphere Development Studio ILE RPG Reference Manual. For example, where I work, we store most of our dates as YYYYMMDD in 8-byte numeric fields. This is the numeric version of the ISO format. I can convert this to a date data type with the following code:

          %date(num_date : *iso)

The "*iso" tells the %date BIF how my numeric date field (num_date) is formatted. We also have a few dates stored as MMDDYYYY in 8-byte numeric fields. In this case all I change is the format on the %date BIF:

          %date(num_date : *usa)

For anyone storing dates in alpha fields, the format you specify will depend on whether or not you store the date separators. For example, if your date is in a 10-byte character field as YYYY-MM-DD (with the dashes), this is again ISO format:

          %date(alpha_date : *iso)

If you do not store standard separators with your date (YYYYMMDD in an 8-byte character field) simply append the number zero (0) to the end of the format name:

          %date(alpha_date : *iso0)

If you store valid separators with your date but not the default for the format (MM-DD-YY in an 8-byte character field), then the default separator is the slash (/) and you indicate the separator at the end of the format name:

          %date(alpha_date : *mdy-)

As you can see, as long as you have your dates in one of the 12 standard formats, it's pretty easy to convert it to a date data type.

You Choose the Date

Now that you have your date as a date data type, it's time to convert it to the format of your choice. To convert a date data-type to an alpha format, use the %CHAR BIF and specify the format that you want to receive. For example, if you want to create a character date as MM/DD/YYYY, specify:

          %char(date_fld : *usa)

If you want the same date but no separators:

          %char(date_fld : *usa0)

The nice thing about using BIFs (and especially in free format) is that you don't need to do separate steps to get your result. Instead, just do it all in one step. If I want to convert from an 8-byte numeric YYYYMMDD date to a 10-character alpha MM/DD/YYYY date I can do it in one statement:

          %char( %date(num_date : *iso) : *usa0)

Remember that the inner conversion takes place first (%date), followed by the outer conversion (%char). The format specified on the %date BIF indicates what you are converting from; the format specified on the %char BIF indicates what you are converting to.

Converting to a numeric field requires just one additional step: convert the alpha date to numeric using the %dec BIF:

          %dec( %char( %date(num_date : *iso) : *usa0) : 8 : 0)

Be sure to specify the correct length, in this case 8-bytes for MMDDYYYY and zero (0) decimal positions. That's how easy it is to convert from any standard format to any other standard format. For specific conversions, see the sample code.

Special Date Values

In addition to converting dates, there are some special dates that you might want to use. I've included code for all of these "special" date values in the sample code. The easiest date value to calculate is the current date: %date(). The sample code also shows how to convert the current date into two numeric formats: USA (MMDDYYYY) and ISO (YYYYMMDD).

Other dates that are important to calculate include the beginning and end of a month, a quarter, or a year. In the sample code, I calculated the beginning of a month as any date minus one less than the day portion of that date. If the date is November 15, then I subtract 14 days to get back to November 1. Each successive calculation is built off of that value. To calculate the end of month date, I add one month to the beginning of month date, which in this case would be December 1, then subtract one day to get November 30, the last day of November.

To find the beginning of quarter date, start again with the beginning of month date and subtract the number of months that equals the remainder from the calculation of one less than the current month divided by three. Using a beginning of month date of November one, the current month is 11, less one leaves 10. Divide that by three and you get three with a remainder of one. That remainder of one is the number of months to subtract from November 1, giving October 1, which is the beginning of the fourth quarter. Similar to the end of month calculation, to find the end of quarter date, add three months, then subtract one day from the beginning of quarter date.

The beginning and end of year calculations are much simpler. If you know the current year, just change the month/day to 01-01 for beginning and 12-31 for end dates . . . no complicated calculations needed.

Using the end of month value, we can calculate the quarter number by dividing the month portion by three, since there are three months in a quarter.

To determine the day of week, first determine the number of days since a known date. I am using the numbers 1-7 to represent the days Monday-Sunday. I have defined a field called "monday" to contain a known Monday date (January 1, 1900). Divide that value by 7, take the remainder and add 1. Any Monday will do, though I prefer to use one far enough in the past to be earlier than any date I will be using in the formula. If you use a date earlier than your "known Monday," your remainder will be negative. Just add 7 to the result to get back into the range of 1-7.

If you prefer to base your day of week on a different range (Sunday-Saturday, for example), just use a different "known date," and the calculation is the same. The only difference is what your "day" number means (1=Sunday if your "known date" is a Sunday).

To determine the day of year (February 1 is the 32nd day of the year), use the %diff BIF to find the difference between the current date and the beginning of year date and add 1.

Finding the week number will depend on your definition of when week one begins. Does week one always start on a particular day of the week? Does it always contain a certain date? I must admit, this calculation was the most challenging for me. The key is to define week one. In the sample code, I am using the ISO 8601 definition, which states that week one is the first week containing four days, which is also the week containing January 4. This standard sometimes places January 1, 2 and 3 into the last week of the previous year. If you use a different method to determine the first week of the year, you will need to modify this process.

As I indicated before, you can combine multiple conversions into a single calculation step. However, because this calculation is rather complicated, I've kept each step separate so that it is easier to comprehend.

Since I know that January 4 is always in week one, I first created a date data type field to contain the date January 4 of the current year. Next, I determine what day of week January 4 falls on. The current week is found by calculating the current day of year number plus the January 4 day of week number plus 2 and dividing that total by 7. In the event that January 4 is on a Monday, Tuesday. or Wednesday, some or all of the earlier days of the year return a week number of zero (0). When that happens, the week number is really 53 from the previous year.

On to i5/OS V5R3

Now that you've seen how to convert dates from and to any standard format and how to calculate some special date values, there are a few other points to consider. First, you can only specify a valid date value when using the %DATE BIF. Many times, a non-existent date value (a shipped date for an order that hasn't shipped yet) contains all zeros, blanks, or nines. You'll need to test your date values before converting or use a MONITOR group to catch errors.

All of the conversions to numeric date fields are fine as is up through OS/400 V5R2. In i5/OS V5R3, the %DEC BIF was changed to allow date data type values to be specified in the first parameter, allowing you to eliminate the %CHAR BIF as part of the conversion. If you code the following to convert the current date to a numeric field:

          %dec( %date() )

the result will be in ISO format because the output of the %date BIF is always in ISO format. If you convert a date data type field as in the following:

          %dec(date_fld)

the result will be based on the format of the date field. If you specify a format on the second parameter, you can indicate the resulting format. For example, if you specified DATFMT(*USA) on the D-spec when defining "date_fld" and coded the following:

          %dec(date_fld : *iso)

the result would be in ISO format.

Create Your Own Functions

I have given examples of all of the date conversions and special date calculations I could think of. Most companies won't need more than a few of these, but which few will depend on your environment. If you have any other date needs, these examples should give you an idea of how to create them. So pick the routines that you value most and create your own functions to suit your business needs.

Bruce Guetzkow has programmed on the AS/400 and iSeries since 1990, in manufacturing, distribution, and other industries. He is currently the IS director at United Credit Service in Elkhorn, Wis. Click here to contact Bruce by e-mail.