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

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;

---

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.

---

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

---

Here's a non-tested port of Steve Kass's solution to Informix.

Steve's solution itself is well tested under MS SQL Server. I like it better than my previous solutions because the conversion to am/pm time is exclusively done algebraically not requiring the help of any branching (with CASE statements and such).

Substitute the @milTime with column name if the numeric "military time" comes from the database. The @ variable is only there for test.

--declare @milTime int
--set @milTime = 1359
SELECT
  CAST(MOD((@milTime /100 + 11), 12) + 1 AS VARCHAR(2))
  ||':'
  ||SUBSTRING(CAST((@milTime%100 + 100) AS CHAR(3)) FROM 2 FOR 2)
  ||' '
  || SUBSTRING('ap' FROM (MOD(@milTime / 1200, 2) + 1) FOR 1)
  || 'm';

For reference here's my [fixed], CASE-based, solution for SQL Server

SELECT 
  CASE ((@milTime / 100) % 12)
      WHEN 0 THEN '12'
      ELSE CAST((@milTime % 1200) / 100 AS varchar(2))
  END 
  + ':' + RIGHT('0' + CAST((@milTime % 100) AS varchar(2)), 2)
  + CASE (@milTime / 1200) WHEN 0 THEN ' am' ELSE ' pm' END

---

Ah, a fellow Jenzabar user (Jonathan, don't be too cruel about the schemas. They are literally decades old). Surprised you didn't ask this on the CX-Tech list. I'd've sent you an RCS-ready stored procedure for CX.

-sw

{
 Revision Information (Automatically maintained by 'make' - DON'T CHANGE)
 -------------------------------------------------------------------------
 $Header$
 -------------------------------------------------------------------------
}
procedure       se_get_inttime
privilege       owner
description     "Get time from an integer field and return as datetime"
inputs          param_time integer      "Integer formatted time"
returns         datetime hour to minute "Time in datetime format"
notes           "Get time from an integer field and return as datetime"

begin procedure

DEFINE tm_str VARCHAR(255);
DEFINE h INTEGER;
DEFINE m INTEGER;

IF (param_time < 0 OR param_time > 2359) THEN
RAISE EXCEPTION -746, 0, "Invalid time format. Should be: 0 - 2359";
END IF

LET tm_str = LPAD(param_time, 4, 0);

LET h = SUBSTR(tm_str, 1, 2);

IF (h < 0 OR h > 23) THEN
RAISE EXCEPTION -746, 0, "Invalid time format. Should be: 0 - 2359";
END IF

LET m = SUBSTR(tm_str, 3, 4);

IF (m < 0 OR m > 59) THEN
RAISE EXCEPTION -746, 0, "Invalid time format. Should be: 0 - 2359";
END IF

RETURN TO_DATE(h || ':' || m , '%R');

end procedure

grant
    execute to (group public)

---

Not sure about informix, here's what I would do in Oracle (some examples, but untested as I'm at home):

1. Turn integer into a string: To_Char (milTime), e.g. 1->'1', 545 -> '545', 1215 -> '1215'
2. Make sure we always have a four character string: Right('0000'||To_Char(milTime), 4), e.g. 1-> '0001', 545 -> '0545', 1215 -> '1215'
3. Turn into a datetime: To_Date (Right('0000'||To_Char(milTime), 4), 'HH24:MI')
4. Output into desired format: To_Char(To_Date(..),'HH:MI AM')e.g. 1->'00:01 AM', 545 -> '05:45 AM', 1215 -> '12:15 PM'

Oracle's To_Date and To_Char are proprietary, but I'm sure that there are standard SQL or Informix functions that achieve the same result without having to resort to "calculations".

---

CheeseWithCheese said it had to be done in an ACE report, so here's my ACE report...

Example of military hour smallint conversion to AM/PM format in ACE:

select beg_tm, end_tm ...

define
variable utime char(4) 
variable ftime char(7)
end

format

on every row

let utime = beg_tm  {cast beg_tm to char(4). do same for end_tm} 

if utime[1,2] = "00" then let ftime[1,3] = "12:"
if utime[1,2] = "01" then let ftime[1,3] = " 1:"
if utime[1,2] = "02" then let ftime[1,3] = " 2:"
if utime[1,2] = "03" then let ftime[1,3] = " 3:"
if utime[1,2] = "04" then let ftime[1,3] = " 4:"
if utime[1,2] = "05" then let ftime[1,3] = " 5:"
if utime[1,2] = "06" then let ftime[1,3] = " 6:"
if utime[1,2] = "07" then let ftime[1,3] = " 7:"
if utime[1,2] = "08" then let ftime[1,3] = " 8:"
if utime[1,2] = "09" then let ftime[1,3] = " 9:"
if utime[1,2] = "10" then let ftime[1,3] = "10:"
if utime[1,2] = "11" then let ftime[1,3] = "11:"

if utime[1,2] = "12" then let ftime[1,3] = "12:"
if utime[1,2] = "13" then let ftime[1,3] = " 1:"
if utime[1,2] = "14" then let ftime[1,3] = " 2:"
if utime[1,2] = "15" then let ftime[1,3] = " 3:"
if utime[1,2] = "16" then let ftime[1,3] = " 4:"
if utime[1,2] = "17" then let ftime[1,3] = " 5:"
if utime[1,2] = "18" then let ftime[1,3] = " 6:"
if utime[1,2] = "19" then let ftime[1,3] = " 7:"
if utime[1,2] = "20" then let ftime[1,3] = " 8:"
if utime[1,2] = "21" then let ftime[1,3] = " 9:"
if utime[1,2] = "22" then let ftime[1,3] = "10:"
if utime[1,2] = "23" then let ftime[1,3] = "11:"

let ftime[4,5] = utime[3,4]   

if utime[1,2] = "00"
or utime[1,2] = "01"
or utime[1,2] = "02"
or utime[1,2] = "03"
or utime[1,2] = "04"
or utime[1,2] = "05"
or utime[1,2] = "06"
or utime[1,2] = "07"
or utime[1,2] = "08"
or utime[1,2] = "09"
or utime[1,2] = "10"
or utime[1,2] = "11" then let ftime[6,7] = "AM"

if utime[1,2] = "12"
or utime[1,2] = "13"
or utime[1,2] = "14"
or utime[1,2] = "15"
or utime[1,2] = "16"
or utime[1,2] = "17"
or utime[1,2] = "18"
or utime[1,2] = "19"
or utime[1,2] = "20"
or utime[1,2] = "21"
or utime[1,2] = "22"
or utime[1,2] = "23" then let ftime[6,7] = "PM"

print column 1, "UNFORMATTED TIME: ", utime," = FORMATTED TIME: ", ftime 

---

LONG-hand approach... but works

select  substr((mtg_rec.beg_tm-1200),0,1)||":"||substr((mtg_rec.beg_tm-1200),2,2)||" pm" beg_tm,
            substr((mtg_rec.end_tm-1200),0,1)||":"||substr((mtg_rec.end_tm-1200),2,2)||" pm" end_tm
    from    mtg_rec
    where   mtg_rec.beg_tm between 1300 and 2159
            and mtg_rec.end_tm between 1300 and 2159
    union
    select  substr((mtg_rec.beg_tm-1200),0,1)||":"||substr((mtg_rec.beg_tm-1200),2,2)||" pm" beg_tm,
            substr((mtg_rec.end_tm-1200),0,2)||":"||substr((mtg_rec.end_tm-1200),3,2)||" pm" end_tm
    from    mtg_rec
    where   mtg_rec.beg_tm between 1300 and 2159
            and mtg_rec.end_tm between 2159 and 2400
    union
    select  substr((mtg_rec.beg_tm-1200),0,2)||":"||substr((mtg_rec.beg_tm-1200),3,2)||" pm" beg_tm,
            substr((mtg_rec.end_tm-1200),0,2)||":"||substr((mtg_rec.end_tm-1200),3,2)||" pm" end_tm
            mtg_rec.days
    from    mtg_rec
    where   mtg_rec.beg_tm between 2159 and 2400
            and mtg_rec.end_tm between 2159 and 2400
    union
     select substr((mtg_rec.beg_tm),0,1)||":"||(substr((mtg_rec.beg_tm),2,2))||" am" beg_tm,
            substr((mtg_rec.end_tm),0,1)||":"||(substr((mtg_rec.end_tm),2,2))||" am" end_tm
            mtg_rec.days
    from    mtg_rec
    where   mtg_rec.beg_tm between 0 and 959
            and mtg_rec.end_tm between 0 and 959
    union
     select substr((mtg_rec.beg_tm),0,2)||":"||(substr((mtg_rec.beg_tm),3,2))||" am" beg_tm,
            substr((mtg_rec.end_tm),0,2)||":"||(substr((mtg_rec.end_tm),3,2))||" am" end_tm
            mtg_rec.days
    from    mtg_rec
    where   mtg_rec.beg_tm between 1000 and 1259
            and mtg_rec.end_tm between 1000 and 1259
    union
     select cast(beg_tm as varchar(4)),
            cast(end_tm as varchar(4))
    from    mtg_rec
    where   mtg_rec.beg_tm = 0
            and mtg_rec.end_tm = 0
    into temp time_machine with no log;