Guru: More Date And Time Conversions Using SQL

March 26, 2018 Ted Holt

Since many, if not most, IBM i shops store dates and times in numeric and character fields, it behooves those of us who program those systems to understand all available date- and time-conversion tools. A conversation with a fellow attendee of the recent RPG & DB2 Summit made me realize that I had not written about certain SQL conversion methods.

IBM i programmers need to convert date, time, and timestamp data from one format to another for at least two reasons. First, we can’t do date and time arithmetic with numeric and character fields. Second, the people whom we serve prefer to read dates and times in formats that they are accustomed to, and we can easily accommodate them.

Converting To Timestamps

Let’s consider as an example a table (physical file) of orders. Sales orders, manufacturing orders, assembly orders, whatever. Any type of order that has a due date column (field) will do. For this illustration, we need to consider two columns only — order number and due date. How order number is defined is irrelevant. How due date is defined is all that matters. To begin, consider first the case when due date is defined as an eight-digit packed- or zoned-decimal field.

Select all orders that are due within the next 30 days, but do not include past-due orders.

select * 
  from orders
 where timestamp_format (char(DueDate), 'YYYYMMDD') between
          current date and (current date + 30 days);

The TIMESTAMP_FORMAT function converts character data to a timestamp based on a pattern specified in a format string. A format string value of YYYYMMDD means that the date is stored as a four-digit year, a two-digit month, and a two-digit day. For more information about TIMESTAMP_FORMAT, read Michael Sansoterra’s superb article about DB2 for i 6.1 enhancements.

Since the due date is numeric, use the CHAR function to convert it to character data.

Some shops store dates in seven-digit packed-decimal columns.

The query is only slightly different.

select * 
  from orders
where timestamp_format (char(DueDate+19000000), 'YYYYMMDD') between
         current date and (current date + 30 days);

Adding 19 million to the date converts it to the YYYYMMDD format of the previous example. TIMESTAMP_FORMAT does not have a way to indicate a one-digit century indicator.

Shops that use eight-byte character dates use an even simpler query, since the date data is already in character format.

select * 
  from orders
 where timestamp_format (DueDate, 'YYYYMMDD') between
         current date and (current date + 30 days);

Converting Timestamps

Sometimes we need to convert in the other direction. We need to convert dates, times, or timestamps to other data types. This is also easy to do. TIMESTAMP_FORMAT has a complement: VARCHAR_FORMAT.

Like TIMESTAMP_FORMAT, VARCHAR_FORMAT accepts two arguments — the data to be converted and a formatting string. See Michael Sansoterra’s article for more information.

Let’s say that the users are not allowed to adjust due dates themselves, but instead have a way to indicate the orders for which due date needs to be adjusted. The order numbers are stored in a table called ORDER01. A batch process applies the update. Here are three forms of the SQL we need, depending on how due date is defined.

-- packed- or zoned-decimal, 8 digits
update orders as ord
   set ord.DueDate = 
         dec(varchar_format (current date + 5 days, 'YYYYMMDD'),8)
 where ord.OrderNbr in (select OrderNbr from order01);

-- packed-decimal, 7 digits
update orders as ord
   set ord.DueDate = 
   dec(varchar_format (current date + 5 days, 'YYYYMMDD'),8) - 19000000
 where ord.OrderNbr in (select OrderNbr from order01);

-- character 8
update orders as ord
   set ord.DueDate = varchar_format (current date + 5 days, 'YYYYMMDD')
 where ord.OrderNbr in (select OrderNbr from order01);

All three queries set the due date to five days after the current date.

These are not the only date-conversion methods. (See my FMTDATE function.) But they are good ones, and I encourage everyone who deals with date and time data to master them.

RELATED STORIES

Treasury of new DB2 6.1 (V6R1) Features, Part 5: New Functions and Change Stamp Columns

IBM Knowledge Center: CHAR

IBM Knowledge Center: TIMESTAMP_FORMAT

IBM Knowledge Center: VARCHAR_FORMAT

Formatting Dates With SQL, Take 3