SQL - Convert 24-hour ("military") time (2145) to "AM / PM time" (21:45)

Question

I have 2 fields that I work with that are stored as small temporary structured times.
Edit I work on IBM Informix Dynamic Server version 10.00.FC9

beg_tm and end_tm

Value Examples

beg_tm   545
end_tm   815

beg_tm   1245
end_tm   1330

Output example

beg_tm   5:45 am
end_tm   8:15 am

beg_tm   12:45 pm
end_tm   1:30 pm

This worked for me in Perl, but I'm looking for a way to do this using SQL and case statements.

Is it possible?

---

EDIT

Essentially, this formatting should be used in the ACE report. I could not find a way to format it in the output section using simple blocks

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

Where vbeg_tm is the declared variable char (4)

---

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;

It works for hours <1200 (sometimes .... 10:40 does not work)

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 A
variation of the cast syntax used in the expression approach of Jonathan Leffler,

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 in SO 440061 there is useful information about the conversion between 12 and 24-hour time symbols (opposite this conversion); this is not trivial, because 12:45 in the morning comes half an hour before 1:15 in the morning.

Further, note that Informix (IDS - Informix Dynamic Server) version 7.31 finally completed its work in 2009-09-30; it is no longer a supported product.

; 7.30.UC1 7.31.UD8, .

TO_CHAR() . IDS 12.10 Information Center, , 7.31 ( 7.30, ).

% R 24- , . " GL_DATETIME, , " % I " 12- , " % p" am/pm. ID1.UDUD8 IDS :

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

---

, , SMALLINT 0000..2359 . , Informix - DATETIME HOUR TO MINUTE - , 3 2, , SMALLINT.

SQL Server:

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

- !

Informix IDS 11.50, , :

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;

SUBSTRING FROM FOR - SQL - , .

:

     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 

: 559-601 , .

IDS 11.50; IDS 7.3x . ; ...

, SQL .., , - , - . , - () , :

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;

Informix '[2,3]' ; , ( , ), ( , ). ; , .

Informix (OnLine 5.x, SE 7.x, IDS 7.x 9.x, 10.00, 11.x, 12.x), .

( ) :

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;

:

     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

SELECT ACE .

---

[ , ...]

IDS 7.31 , MOD(). , - :

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;

IDS 7.31.UD8 Solaris 10 . ; , - . , , ; , - ​​ .

Answer 2

Informix.

Steve MS SQL Server. , , am/pm , ( CASE ..).

@milTime , " " . @ .

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

[], CASE 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

Answer 3

mjv - . (, 0001 0: 1 , .)

T-SQL, . , SUBSTRING.

2400 (12:00), .

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

Answer 4

, Jenzabar (, , ). , CX-Tech. RCS 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)

Answer 5

informix, Oracle ( , , ):

• : To_Char (milTime), . 1 → '1', 545 → '545', 1215 → '1215'
• , : Right('0000'||To_Char(milTime), 4), . 1- > '0001', 545 → '0545', 1215 → '1215'
• : To_Date (Right('0000'||To_Char(milTime), 4), 'HH24:MI')
• : To_Char(To_Date(..),'HH:MI AM') . 1 → '00:01 AM', 545 → '05: 45 AM ', 1215 → '12: 15 PM'

Oracle To_Date To_Char , , SQL Informix, , "".

Answer 6

CheeseWithCheese , ACE, ACE...

smallint AM/PM 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 

Answer 7

LONG- ...

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;