SQL - Converting 24-hour ("military") time (2145) to "AM/PM time" (9:45 pm)
- by CheeseConQueso
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;
