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

Question

I have 2 fields I'm working with that are stored as smallint military structured times.
Edit I'm running on IBM Informix Dynamic Server Version 10.00.FC9

beg_tm and end_tm

Sample values

beg_tm   545
end_tm   815

beg_tm   1245
end_tm   1330

Sample output

beg_tm   5:45 am
end_tm   8:15 am

beg_tm   12:45 pm
end_tm   1:30 pm

I had this working in Perl, but I'm looking for a way to do it with SQL and case statements.

Is this even possible?

---

EDIT

Essentially, this formatting has to be used in an ACE report. I couldn't find a way to format it within the output section using simple blocks of

if(beg_tm>=1300) then
beg_tm = vbeg_tm - 1200

Where vbeg_tm is a declared char(4) variable

---

EDIT This works for hours >=1300 (EXCEPT FOR 2230 !!)

select substr((beg_tm-1200),0,1)||":"||substr((beg_tm-1200),2,2) from mtg_rec where beg_tm>=1300;

This works for hours < 1200 (sometimes.... 10:40 is failing)

select substr((mtg_rec.beg_tm),0,(length(cast(beg_tm as varchar(4)))-2))||":"||(substr((mtg_rec.beg_tm),2,2))||" am" beg_tm from mtg_rec where mtg_no = 1;

---

EDIT
Variation of casting syntax used in Jonathan Leffler's expression approach

SELECT  beg_tm,
        cast((MOD(beg_tm/100 + 11, 12) + 1) as VARCHAR(2)) || ':' ||
        SUBSTRING(cast((MOD(beg_tm, 100) + 100) as CHAR(3)) FROM 2) ||
        SUBSTRING(' am pm' FROM (MOD(cast((beg_tm/1200) as INT), 2) * 3) + 1 FOR 3),
        end_tm,
        cast((MOD(end_tm/100 + 11, 12) + 1) as VARCHAR(2)) || ':' ||
        SUBSTRING(cast((MOD(end_tm, 100) + 100) as CHAR(3)) FROM 2) ||
        SUBSTRING(' am pm' FROM (MOD(cast((end_tm/1200) as INT), 2) * 3) + 1 FOR 3)
      FROM mtg_rec
      where mtg_no = 39;

Answer 1

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.

Answer 2

mjv's second try still doesn't work. (For 0001 it gives 0:1 am, for example.)

Here's a T-SQL solution that should work better. It can be adapted to other dialects by using the appropriate syntax for concatenation and SUBSTRING.

It also works for the military time 2400 (12:00 am), which might be useful.

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';