Date and Time Formats and Query/400

Hey, Ted:

I recently received the following request from a user: "On my report that shows when an order was changed, the time prints as 152833. Could we please just say 3:28PM? My answer: "Sure can."

Even though Query/400 can add colons (:) for HH:MM:SS in the field editing, it can't convert military time to a.m. and p.m. At least, I've never found a way. But adding a few defined fields will convert it for you.

Field       Expression                         Len   Dec
==========  =================================  ===  ===
TABLE       '12AM 1AM 2AM 3AM 4AM 5AM 6AM 7AM
             8AM 9AM10AM11AM12PM 1PM 2PM 3PM
            4PM 5PM 6PM 7PM 8PM 9PM10PM11PM'

INDEX       OPTIME / 10000                      2     0

NEWTIME     SUBSTR(TABLE,INDEX*4+1,2)||':'||
            SUBSTR(DIGITS(OPTIME),3,2)||
            SUBSTR(TABLE,INDEX*4+3,2)

For this to work, set the "use rounding?" option, in the Specify Processing Options panel, to N.

The hour serves as a look-up index to a table, to convert the time format. I like this example because it requires a single index and table to select two different data types--both the hour and the a.m./p.m. designator.

--Bob Ellsworth

Thanks for the tip, Bob. Since you and I first swapped e-mail a few years ago, you've never ceased to amaze me by making Query do more than its developers intended. Your tips remind me of Samuel Johnson's comment about women preachers: "A woman's preaching is like a dog's walking on his hinder legs. It is not done well; but you are surprised to find it done at all."

I made some small changes to your query. First, I added a space before AM or PM, out of personal preference. Second, by using IBM's official way to convert character to numeric--the microsecond function--I eliminated the need to turn off rounding. By the way, if I'm not mistaken, it was you who showed me that use of the microsecond function.

While I was editing, I also added a second example that converts an eight-digit numeric date in YYYYMMDD format to the form DD-MMM-YYYY, where MMM is a three-character month abbreviation. Here are the result fields.

Field       Expression                         Len  Dec 
==========  =================================  ===  ===
TTABLE      '12AM 1AM 2AM 3AM 4AM 5AM 6AM 7AM
             8AM 9AM10AM11AM12PM 1PM 2PM 3PM
            4PM 5PM 6PM 7PM 8PM 9PM10PM11PM'

HOUR        microsecond('2000-01-01-01.01.01.    2    0
            0000'||substr(digits(optime),1,2)
            )

NEWTIME     SUBSTR(TTABLE,HOUR*4+1,2)||':'||
            SUBSTR(DIGITS(OPTIME),3,2)||' '||
            SUBSTR(TTABLE,HOUR*4+3,2)

MTABLE      'JanFebMarAprMayJunJulAugSepOctNo
            vDec'

MONTH       microsecond('2000-01-01-01.01.01.
            0000'||substr(digits(opdate),5,2)
            )

NEWDATE     substr(digits(opdate),7,2)||'-'||
            substr(mtable,((month-1)*3+1),3)
            ||'-'||substr(digits(opdate),1,4)

Here is an example report that shows the original data and the converted dates and times.

20,030,806   06-Aug-2003     73,456    7:34 AM 
19,990,907   07-Sep-1999     80,000    8:00 AM 
20,031,005   05-Oct-2003     95,522    9:55 AM 
20,021,130   30-Nov-2002    100,422   10:04 AM 
20,001,004   04-Oct-2000    151,515    3:15 PM 
20,050,505   05-May-2005    201,918    8:19 PM
20,010,412   12-Apr-2001    213,030    9:30 PM
19,960,317   17-Mar-1996    224,455   10:44 PM
20,040,205   05-Feb-2004    231,817   11:18 PM

--Ted

Sponsored By DAMON TECHNOLOGIES

The Next Evolution of RPG is Here! RSP (RPG Server Pages) is the best way to develop Web applications with RPG. Developers use their existing RPG skills. More robust than CGI with greater flexibility. RSP is not just visual development. It is an application server built specifically for the iSeries. Full debug capabilities. Session Handling with a built in garbage collector. The most cost effective solution for the iSeries. With RSP, Web content is developed with the Ease, Speed, and Reliability of RPG. In today's fast paced business world, there is not enough time or resources to convert RPG developers into Java developers. The logical step to bring your business critical applications to the Web is with RSP. RSP gives the developer the tools necessary to create fast and reliable Web applications. Download your FREE copy of RSP today! www.damontech.com