SQL - 将 24 小时（“军事”）时间（2145）转换为“上午/下午时间”（晚上 9:45）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1572927/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
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.
EditI'm running on IBM Informix Dynamic Server Version 10.00.FC9
我有 2 个正在使用的字段，它们存储为 smallint 军事结构时间。
编辑我在 IBM Informix Dynamic Server 版本 10.00.FC9 上运行
beg_tm and end_tm
beg_tm 和 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.
我在 Perl 中使用过这个，但我正在寻找一种使用 SQL 和 case 语句来完成它的方法。
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
本质上，这种格式必须在 ACE 报告中使用。我找不到使用简单块在输出部分中对其进行格式化的方法
if(beg_tm>=1300) then
beg_tm = vbeg_tm - 1200
Where vbeg_tm is a declared char(4) variable
其中 vbeg_tm 是声明的 char(4) 变量
EDIT编辑这适用于 >=1300 小时（除了 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)
这适用于 < 1200 小时（有时...... 10:40 失败）
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编辑
乔纳森莱夫勒表达式方法中使用的铸造语法的变化
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;
回答by Jonathan Leffler
Please note that there is useful information at SO 440061about 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.
请注意，SO 440061 中有关于在 12 小时和 24 小时表示法之间转换的有用信息（与此转换相反）；这不是微不足道的，因为 12:45 am 比凌晨 1:15 早半小时。
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.
接下来请注意，Informix（IDS——Informix Dynamic Server）7.31版本终于在2009-09-30结束服务；它不再是受支持的产品。
You should be more precise with your version number; there are considerable differences between 7.30.UC1 and 7.31.UD8, for instance.
您应该更准确地使用您的版本号；例如，7.30.UC1 和 7.31.UD8 之间存在相当大的差异。
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).
但是，您应该能够根据需要使用TO_CHAR()函数来格式化时间。尽管此参考是IDS 12.10 信息中心，但我相信您将能够在 7.31 中使用它（不一定在 7.30 中，但在过去十年的大部分时间里您不应该使用它）。
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:
它说，有一个 24 小时制的“%R”格式说明符。它还向您推荐“ GL_DATETIME”，它说“%I”为您提供 12 小时时间，“%p”为您提供上午/下午指示符。我还找到了一个 7.31.UD8 的 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
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.
我从重新阅读这个问题中看到，您实际上有 0000..2359 范围内的 SMALLINT 值，并且需要将它们转换。通常，我会指出 Informix 有一种用于存储此类值的类型 - DATETIME HOUR TO MINUTE - 但我承认它在磁盘上占用了 3 个字节而不是 2 个字节，因此它不像 SMALLINT 表示法那样紧凑。
Steve Kass showed the SQL Server notation:
Steve Kass 展示了 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';
The trick for getting the hour correct is neat - thanks Steve!
获得正确时间的技巧很巧妙——谢谢史蒂夫！
Translated into Informix for IDS 11.50, assuming that the table is:
翻译成Informix for 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;
The SUBSTRING notation using FROM and FOR is standard SQL notation - weird, but so.
使用 FROM 和 FOR 的 SUBSTRING 表示法是标准的 SQL 表示法 - 很奇怪，但确实如此。
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.
注意：值 559-601 在列表中，因为在没有转换为整数的情况下，我遇到了舍入而不是截断的问题。
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...
现在，这是在 IDS 11.50 上测试的；IDS 7.3x 没有强制转换符号。然而，这不是问题。下一条评论将解决这个问题......
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:
作为如何在没有条件等的情况下在 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;
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.
Informix '[2,3]' 表示法是子字符串运算符的原始形式；原始因为（出于我仍然无法理解的原因）下标必须是文字整数（不是变量，不是表达式）。它碰巧在这里很有用；总的来说，这是令人沮丧的。
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.
此存储过程应该适用于您可以使用的任何 Informix 版本（OnLine 5.x、SE 7.x、IDS 7.x 或 9.x、10.00、11.x、12.x）。
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.
现在可以在 ACE 报告中的单个 SELECT 语句中多次使用此存储过程，无需多说。
[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:
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;
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 alwayscrucial 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.
这已在 Solaris 10 上的 IDS 7.31.UD8 上进行了测试，并且工作正常。我不明白报告的语法错误；但是存在版本依赖性的外部可能性 -报告版本号和平台以防万一总是至关重要的。请注意，我很小心地记录了各种事情在哪里起作用；这不是偶然，也不是大惊小怪——这是基于多年的经验。
回答by mjv
Here's a non-testedport of Steve Kass's solution to Informix.
这是 Steve Kass 的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 algebraicallynot requiring the help of any branching(with CASE statements and such).
Steve 的解决方案本身在 MS SQL Server 下经过了很好的测试。我比我以前的解决方案更喜欢它，因为转换为 am/pm 时间是完全代数完成的，不需要任何分支的帮助（使用 CASE 语句等）。
Substitute the @milTime with column name if the numeric "military time" comes from the database. The @ variable is only there for test.
如果数字“军事时间”来自数据库，则用列名替换@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';
For reference here's my [fixed], CASE-based, solution for SQL Server
作为参考，这里是我的 [fixed]、基于 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
回答by Steve Kass
mjv's second try still doesn't work. (For 0001 it gives 0:1 am, for example.)
mjv 的第二次尝试仍然不起作用。（例如，对于 0001，它给出 0:1 am。）
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.
这是一个应该更好用的 T-SQL 解决方案。通过使用适当的连接和 SUBSTRING 语法，它可以适应其他方言。
It also works for the military time 2400 (12:00 am), which might be useful.
它也适用于军事时间 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';
回答by Steve Kass
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.
啊，一位 Jenzabar 用户（乔纳森，不要对模式太残忍。它们实际上已经有几十年的历史了）。很惊讶你没有在 CX-Tech 列表上问这个。我已经向您发送了一个用于 CX 的 RCS 就绪存储过程。
-sw
-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)
回答by Frank R.
CheeseWithCheese said it had to be done in an ACE report, so here's my ACE report...
CheeseWithCheese 说它必须在 ACE 报告中完成，所以这是我的 ACE 报告......
Example of military hour smallint conversion to AM/PM format in ACE:
ACE 中将军事小时 smallint 转换为 AM/PM 格式的示例：
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
回答by Thorsten
Not sure about informix, here's what I would do in Oracle (some examples, but untested as I'm at home):
不确定informix，这是我在Oracle 中会做的事情（一些示例，但我在家时未经测试）：
- Turn integer into a string:
To_Char (milTime), e.g. 1->'1', 545 -> '545', 1215 -> '1215' - Make sure we always have a four character string:
Right('0000'||To_Char(milTime), 4), e.g. 1-> '0001', 545 -> '0545', 1215 -> '1215' - Turn into a datetime:
To_Date (Right('0000'||To_Char(milTime), 4), 'HH24:MI') - 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'
- 将整数转为字符串：
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'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".
Oracle 的 To_Date 和 To_Char 是专有的，但我确信有标准的 SQL 或 Informix 函数可以实现相同的结果，而不必求助于“计算”。
回答by CheeseConQueso
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;
