USA Time Format in Query for i, Redux
October 6, 2010 Hey, Ted
|
I am having trouble converting my time format from military time. That is, the time fields in my system are six-digit numeric fields. I want to print them in the common AM-PM format we use in the United States. –Ronnie I’ve covered this topic twice that I know of. Both tips ran in 2003. (See Date and Time Formats and Query/400 and Miscellaneous Query Tips. I’ll cover it again today, because some readers who might like to know about this technique didn’t see it then and don’t know it’s out there, and because I have a slight improvement on Bob Ellsworth’s slick technique. The easy way to show a time in USA format was in the October 29, 2003, issue of Four Hundred Guru. It involves converting numeric to time to character, like this:
Field Expression
========= =================================
JB_DIGITS digits(jobstart)
JB_CHAR substr(jb_digits,1,2)||':'||
substr(jb_digits,3,2)||':'||
substr(jb_digits,5,2)
JB_NEWTIME char(time(jb_char),usa)
The numeric field is JOBSTART. The formatted time is JS_NEWTIME. There are two things that I don’t like about this method. First, it does not drop leading zeros from the hour. Second, it prints a zero time as 00:00 AM, not 12:00 AM. Here is a messy method, but it will give you what you want.
Field Expression
========= =================================
JS_DIGITS digits(jobstart)
JS_HR hour(timestamp('2000-01-01-'||
substr(js_digits,1,2)||
'.00.00.000000'))
JS_MIN_C substr(js_digits,3,2)
TABHOURS '12 1 2 3 4 5 6 7 8 9101112 1 2 3
4 5 6 7 8 9101112'
TABAMPM 'AMAMAMAMAMAMAMAMAMAMAMAMPMPMPMPM
PMPMPMPMPMPMPMPMPM'
JS_NEWTIME substr(tabhours,js_hr*2+1,2)||
':'||js_min_c||' '||
substr(tabampm,js_hr*2+1,2)
Here’s a comparison of the numeric value and the edited value. 000000 12:00 AM 000101 12:01 AM 001001 12:10 AM 005959 12:59 AM 023125 2:31 AM 024459 2:44 AM 024500 2:45 AM 024959 2:49 AM 025000 2:50 AM 030405 3:04 AM 080000 8:00 AM 090110 9:01 AM 113500 11:35 AM 115959 11:59 AM 120000 12:00 PM 120000 12:00 PM 131544 1:15 PM 150405 3:04 PM 235959 11:59 PM 240000 12:00 AM The things we do to give the users what they want! As the old saying goes, give ’em 2.54 centimeters, and they’ll take 1.609344 kilometers. –Ted RELATED STORIES Date and Time Formats and Query/400
|
