• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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

    Calculate Ages with Query/400

    Date and Time Formats and Query/400

    Miscellaneous Query Tips



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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Midrange Dynamics North America

    Want to deliver DevOps on IBM i?

    DevOps enables your IBM i development teams to shorten the software development lifecycle while delivering features, fixes, and frequent updates that are closely aligned with business objectives. Flexible configuration options within MDChange make it easy to adapt to new workflow strategies and policies as you adopt DevOps practices across your organization.

    Learn More.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Help/Systems:  FREE Webinar. Oct. 7, 9 a.m. CST. Robot/REPORTS: Satisfy Your Audit Requirements
    System i Developer:  RPG & DB2 Summit in Minneapolis, October 12-14 for 3 days of serious training
    COMMON:  Join us at the 2011 IT Executive Conference, May 1-3, in Minneapolis, MN

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    Chip Wars: List Price, $29.95

    Tango/04 Touts International Sales IBM i Dominates the CPW Capacity Budget

    Leave a Reply Cancel reply

Volume 10, Number 30 -- October 6, 2010
THIS ISSUE SPONSORED BY:

SEQUEL Software
WorksRight Software
inFORM Decisions

Table of Contents

  • Automate E-Mail Operations with Outlook and VBA
  • USA Time Format in Query for i, Redux
  • Admin Alert: Getting Started with i/OS Security Auditing, Part 2

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • The Power11 Transistor Count Discrepancies Explained – Sort Of
  • Is Your IBM i HA/DR Actually Tested – Or Just Installed?
  • Big Blue Delivers IBM i Customer Requests In ACS Update
  • New DbToo SDK Hooks RPG And Db2 For i To External Services
  • IBM i PTF Guide, Volume 27, Number 33
  • Tool Aims To Streamline Git Integration For Old School IBM i Devs
  • IBM To Add Full System Replication And FlashCopy To PowerHA
  • Guru: Decoding Base64 ASCII
  • The Price Tweaking Continues For Power Systems
  • IBM i PTF Guide, Volume 27, Numbers 31 And 32

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle