SQL - Converting 24-Hour ("Military") Time (2145) to "Am/Pm Time" (9:45 Pm)

SQL - Converting 24-hour (military) time (2145) to AM/PM time (9:45 pm)

Please note that there is useful information at SO 440061 about converting between 12 hour and 24 hour notations for time (the opposite of this conversion); it isn't trivial, because 12:45 am comes half an hour before 1:15 am.

Next, please note that Informix (IDS — Informix Dynamic Server) version 7.31 finally reached its end of service on 2009-09-30; it is no longer a supported product.

You should be more precise with your version number; there are considerable differences between 7.30.UC1 and 7.31.UD8, for instance.

However, you should be able to use the TO_CHAR() function to format times as you need. Although this reference is to the IDS 12.10 Information Center, I believe that you will be able to use it in 7.31 (not necessarily in 7.30, but you should not have been using that for most of the last decade).

There is a '%R' format specifier for 24-hour time, it says. It also refers you to 'GL_DATETIME', where it says '%I' gives you the 12-hour time and '%p' gives you the am/pm indicator. I also found a 7.31.UD8 instance of IDS to validate this:

select to_char(datetime(2009-01-01 16:15:14) year to second, '%I:%M %p')
    from dual;

04:15 PM

select to_char(datetime(2009-01-01 16:15:14) year to second, '%1.1I:%M %p')
    from dual;

4:15 PM

---

I see from re-reading the question that you actually have SMALLINT values in the range 0000..2359 and need to get those converted. Often, I'd point out that Informix has a type for storing such values - DATETIME HOUR TO MINUTE - but I concede it occupies 3 bytes on disk instead of just 2, so it isn't as compact as a SMALLINT notation.

Steve Kass showed the SQL Server notation:

select
  cast((@milTime/100+11)%12+1 as varchar(2))
 +':'
 +substring(cast((@milTime%100+100) as char(3)),2,2)
 +' '
 +substring('ap',@milTime/1200%2+1,1)
 +'m';

The trick for getting the hour correct is neat - thanks Steve!

Translated into Informix for IDS 11.50, assuming that the table is:

CREATE TEMP TABLE times(begin_tm SMALLINT NOT NULL);

SELECT  begin_tm,
        (MOD(begin_tm/100 + 11, 12) + 1)::VARCHAR(2) || ':' ||
        SUBSTRING((MOD(begin_tm, 100) + 100)::CHAR(3) FROM 2) || ' ' ||
        SUBSTRING("ampm" FROM (MOD((begin_tm/1200)::INT, 2) * 2) + 1 FOR 2)
      FROM times
      ORDER BY begin_tm;

The SUBSTRING notation using FROM and FOR is standard SQL notation - weird, but so.

Example results:

     0    12:00 am 
     1    12:01 am 
    59    12:59 am 
   100    1:00 am  
   559    5:59 am  
   600    6:00 am  
   601    6:01 am  
   959    9:59 am  
  1000    10:00 am 
  1159    11:59 am 
  1200    12:00 pm 
  1201    12:01 pm 
  1259    12:59 pm 
  1300    1:00 pm  
  2159    9:59 pm  
  2200    10:00 pm 
  2359    11:59 pm 
  2400    12:00 am 

Caution: the values 559-601 are in the list because I ran into a problem with rounding instead of truncation in the absence of the cast to integer.

Now, this was tested on IDS 11.50; IDS 7.3x won't have the cast notation.
However, that isn't a problem; the next comment was going to deal with that...

As an exercise in how to write the expression in SQL without conditionals, etc, this is interesting, but if anyone wrote that more than once in an entire suite, I'd shoot them for lack of modularization. Clearly, this requires a stored procedure - and a stored procedure doesn't need the (explicit) casts or some of the other trickery, though the assignments enforce implicit casts:

CREATE PROCEDURE ampm_time(tm SMALLINT) RETURNING CHAR(8);
    DEFINE hh SMALLINT;
    DEFINE mm SMALLINT;
    DEFINE am SMALLINT;
    DEFINE m3 CHAR(3);
    DEFINE a3 CHAR(3);
    LET hh = MOD(tm / 100 + 11, 12) + 1;
    LET mm = MOD(tm, 100) + 100;
    LET am = MOD(tm / 1200, 2);
    LET m3 = mm;
    IF am = 0
    THEN LET a3 = ' am';
    ELSE LET a3 = ' pm';
    END IF;
    RETURN (hh || ':' || m3[2,3] || a3);
END PROCEDURE;

The Informix '[2,3]' notation is a primitive form of sub-string operator; primitive because (for reasons that still elude me) the subscripts must be literal integers (not variables, not expressions). It happens to work usefully here; in general, it is frustrating.

This stored procedure should work on any version of Informix (OnLine 5.x, SE 7.x, IDS 7.x or 9.x, 10.00, 11.x, 12.x) that you can lay hands on.

To illustrate the equivalence of (a minor variant on) the expression and the stored procedure:

SELECT  begin_tm,
        (MOD(begin_tm/100 + 11, 12) + 1)::VARCHAR(2) || ':' ||
        SUBSTRING((MOD(begin_tm, 100) + 100)::CHAR(3) FROM 2) ||
        SUBSTRING(' am pm' FROM (MOD((begin_tm/1200)::INT, 2) * 3) + 1 FOR 3),
        ampm_time(begin_tm)
      FROM times
      ORDER BY begin_tm;

Which produces the result:

     0  12:00 am        12:00 am
     1  12:01 am        12:01 am
    59  12:59 am        12:59 am
   100  1:00 am         1:00 am 
   559  5:59 am         5:59 am 
   600  6:00 am         6:00 pm 
   601  6:01 am         6:01 pm 
   959  9:59 am         9:59 pm 
  1000  10:00 am        10:00 pm
  1159  11:59 am        11:59 pm
  1200  12:00 pm        12:00 pm
  1201  12:01 pm        12:01 pm
  1259  12:59 pm        12:59 pm
  1300  1:00 pm         1:00 pm 
  2159  9:59 pm         9:59 pm 
  2200  10:00 pm        10:00 pm
  2359  11:59 pm        11:59 pm
  2400  12:00 am        12:00 am

This stored procedure can now be used multiple times in a single SELECT statement inside your ACE report without further ado.

---

[After comments from the original poster about not working...]

IDS 7.31 doesn't handle non-integer values passed to the MOD() function. Consequently, the divisions have to be stored in an explicit integer variable - thus:

CREATE PROCEDURE ampm_time(tm SMALLINT) RETURNING CHAR(8);
    DEFINE i2 SMALLINT;
    DEFINE hh SMALLINT;
    DEFINE mm SMALLINT;
    DEFINE am SMALLINT;
    DEFINE m3 CHAR(3);
    DEFINE a3 CHAR(3);
    LET i2 = tm / 100;
    LET hh = MOD(i2 + 11, 12) + 1;
    LET mm = MOD(tm, 100) + 100;
    LET i2 = tm / 1200;
    LET am = MOD(i2, 2);
    LET m3 = mm;
    IF am = 0
    THEN LET a3 = ' am';
    ELSE LET a3 = ' pm';
    END IF;
    RETURN (hh || ':' || m3[2,3] || a3);
END PROCEDURE;

This was tested on IDS 7.31.UD8 on Solaris 10 and worked correctly. I don't understand the syntax error reported; but there is an outside chance of there being a version dependency - it is always crucial to report version numbers and platforms just in case. Notice that I'm careful to document where various things worked; that isn't an accident, nor is it just fussiness -- it is based on many years of experience.

am pm display in Informix 4GL

You'll need to map the database values ('A' ⟶ 'AM', 'P' ⟶ 'PM') and display the mapped value to a FORMONLY field. The field can be used for input if you want. You can add form attributes such as UPSHIFT, AUTONEXT, INCLUDE = ('AM', 'PM') to the field in the form. You just won't be able to use the RECORD LIKE Table.* notation because the field in the DB is CHAR(1) but you need CHAR(2) for display and input.

More seriously, you'll need to consider whether you have to futz with associated time field. If it is a DATETIME field, the time will be in 24 hour notation, and the conversion from 24 hour to AM/PM notation is not entirely straight-forward. Hint: 00:01:02 in 24 hour clock is 12:01:02 AM; 12:02:03 in 24 hour notation is 12:02:03 PM, but 13:03:04 in 24 hour is 1:02:03 PM in AM/PM notation (usually without the leading zero on the hours). See also Converting 24-hour military time to AM/PM time.

If the time field is simply a string, then you probably don't have these problems, but you'll want to design your form carefully to only accept numeric characters (attributes FORMAT and PICTURE?).

Convert 12-hour date/time to 24-hour date/time

Using Perl and hand-crafted regexes instead of facilities like strptime:

#!/bin/perl -w
while (<>)
{
    # for date times that don't use leading zeroes, use this regex instead:
    # (?:\d{1,2}/\d{1,2}/\d{4} )(\d{1,2})(?::\d\d:\d\d) (AM|PM)
    while (m%(?:\d\d/\d\d/\d{4} )(\d\d)(?::\d\d:\d\d) (AM|PM)%)
    {
        my $hh = $1;
        $hh -= 12 if ($2 eq 'AM' && $hh == 12);
        $hh += 12 if ($2 eq 'PM' && $hh != 12);
        $hh = sprintf "%02d", $hh;
        # for date times that don't use leading zeroes, use this regex instead:
        # (\d{1,2}/\d{1,2}/\d{4} )(\d{1,2})(:\d\d:\d\d) (?:AM|PM)
        s%(\d\d/\d\d/\d{4} )(\d\d)(:\d\d:\d\d) (?:AM|PM)%$1$hh$3%;
    }
    print;
}

That's very fussy - but also converts possibly multiple timestamps per line.

Note that the transformation for AM/PM to 24-hour is not trivial.

• 12:01 AM --> 00:01
• 12:01 PM --> 12:01
• 01:30 AM --> 01:30
• 01:30 PM --> 13:30

Now tested:

perl ampm-24hr.pl <<!
12/24/2005 12:01:00 AM
09/22/1999 12:00:00 PM
12/12/2005 01:15:00 PM
01/01/2009 01:56:45 AM
12/30/2009 10:00:00 PM
12/30/2009 10:00:00 AM
!

12/24/2005 00:01:00
09/22/1999 12:00:00
12/12/2005 13:15:00
01/01/2009 01:56:45
12/30/2009 22:00:00
12/30/2009 10:00:00

---

Added:

In What is a Simple Way to Convert Between an AM/PM Time and 24 hour Time in JavaScript, an alternative algorithm is provided for the conversion:

$hh = ($1 % 12) + (($2 eq 'AM') ? 0 : 12);

Just one test...probably neater.

Convert time stored as number to string

Try Below

SELECT TO_DATE(TO_CHAR(1600,'0000'),'HH24MI') FROM DUAL;

SQL CASE statements on Informix - Can you set more than one field in the END section of a case block?

Normally, I'd ask for the version of Informix that you're using, but it probably doesn't matter much this time. The simple answer is 'No'.

A more complex answer might discuss using a row type constructor, but that probably isn't what you want on the output. And, given the foregoing, then the UPDATE isn't going to work (and would require an extra level of parentheses if it was going to).

Can't create Informix stored procedure using ISQL command?

This problem is DB-Access vs ISQL.

ISQL has a warped sense of humour and thinks that the syntax of SQL still matches what was current with Informix OnLine 4.10 (or, in those days, INFORMIX-OnLine 4.10). Specifically, it doesn't know that stored procedures are made up of multiple statements separated by semi-colons and mis-assumes that SQL statements end at the first semi-colon not in a string or comment.

Workarounds:

• Use DB-Access instead of ISQL to create stored procedures.
• Obtain SQLCMD from the IIUG Software Archive and use that instead.
• Use 'mkproc' from the SQLCMD software to create stored procedures.

Of these, the easiest is to use DB-Access (aka dbaccess - found in $INFORMIXDIR/bin where the server software lives).

---

Related Topics