• 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:

    • Share on Reddit (Opens in new window) Reddit
    • Share on Facebook (Opens in new window) Facebook
    • Share on LinkedIn (Opens in new window) LinkedIn
    • Share on X (Opens in new window) X
    • Email a link to a friend (Opens in new window) Email

    Tags:

    Sponsored by
    ARCAD Software

    AI is opening new possibilities for IBM i modernization, but for business-critical applications, performance alone is not enough: managing risks remains essential.

    How do BOB, Claude, and ARCAD Transformer RPG compare in converting RPG code to free-form RPG?

    Join Alan Ashley, Sr. Solutions Architect, as he presents a hands-on benchmark comparing these three solutions.

    Beyond conversion performance, discover how to minimize modernization risks through automated regression testing, validating application behavior and detecting unintended changes before they impact business operations.

    During this session, you’ll discover:

    • Compare code conversion performance: BOB vs. Claude vs. ARCAD Transformer RPG
    • Understand the role of deterministic automation alongside AI in a comprehensive IBM i modernization strategy
    • Accelerate RPG code transformation with predictable, rule-based processes
    • Reduce business risks through automated regression testing with ARCAD Verifier
    • Build a controlled, auditable modernization process that protects business-critical applications

    Register now for this webinar to discover how ARCAD Software’s deterministic modernization solutions complement AI initiatives, providing a structured, repeatable, and controlled approach to application transformation.

    Share this:

    • Share on Reddit (Opens in new window) Reddit
    • Share on Facebook (Opens in new window) Facebook
    • Share on LinkedIn (Opens in new window) LinkedIn
    • Share on X (Opens in new window) X
    • Email a link to a friend (Opens in new window) 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

    Automate E-Mail Operations with Outlook and VBA Admin Alert: Getting Started with i/OS Security Auditing, Part 2

    Leave a ReplyCancel 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

  • IBM i Delivers New Development Tooling With 2026 Fall Tech Refreshes
  • Big Blue Says AI Is Not Behind The Big Uptick In IBM i Security Alerts
  • Guru: Web Service With Pagination
  • The GenAI Revolution And Product Shortages Send IT Budgets Skyward
  • IBM i PTF Guide, Volume 28, Number 40
  • IBM i PTF Guide, Volume 28, Number 41
  • Bob Code Assistant Gets Better Understanding Of IBM i Applications
  • IBM i In The Cloud And The Case For Reversing Replication
  • Guru: From Hiking Boots To Long-Term Memory
  • Inside IBM’s New PowerHA Cloud Offering, Full System Manager

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