oracle ORA-01849: 小时必须在 1 到 12 之间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28742134/
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
ORA-01849: hour must be between 1 and 12
提问by Yogesh
WHERE (ResTRRequest.RequestTime BETWEEN TO_CHAR(TRUNC(TO_DATE('2012-12-01 20:10:10', 'HH')), 'YYYY-MM-DD HH24:MI:SS')
AND TO_CHAR(TRUNC(CURRENT_TIMESTAMP, 'HH') + INTERVAL '59:59' MINUTE TO SECOND, 'YYYY-MM-DD HH24:MI:SS'))
I have above where condition in query when i execute it,it gives me hours must be between 1 to 12 due to static date I have given i.e ''2012-12-01 20:10:10', 'HH')' if I put sysdate the its working fine but due to static date it gives me error.
当我执行它时，我有上面查询中的 where 条件，由于我给出的静态日期，它给我的时间必须在 1 到 12 之间，即 ''2012-12-01 20:10:10', 'HH')' 如果我把 sysdate 放在它的工作正常但由于静态日期它给了我错误。
回答by David Faber
Let's break this down a bit:
让我们把它分解一下：
WHERE (ResTRRequest.RequestTime
BETWEEN TO_CHAR(TRUNC(TO_DATE('2012-12-01 20:10:10', 'HH')), 'YYYY-MM-DD HH24:MI:SS')
AND TO_CHAR(TRUNC(CURRENT_TIMESTAMP, 'HH') + INTERVAL '59:59' MINUTE TO SECOND, 'YYYY-MM-DD HH24:MI:SS'))
In the first place, I don't think you mean this: TRUNC(TO_DATE('2012-12-01 20:10:10', 'HH')), I think maybe you mean this: TRUNC(TO_DATE('2012-12-01 20:10:10'), 'HH'). The number from 1-12 error comes from the fact that you have an hour of 20and are trying to convert it into a date with the mask of HH. But as I said I think that's a typo. You can also use a TIMESTAMPliteral here rather than TO_DATE():
首先，我不认为你的意思是这个: TRUNC(TO_DATE('2012-12-01 20:10:10', 'HH'))，我想你可能是这个意思: TRUNC(TO_DATE('2012-12-01 20:10:10'), 'HH')。1-12 错误的数字来自这样一个事实，即您有一个小时的时间20并试图将其转换为掩码为HH. 但正如我所说，我认为这是一个错字。您也可以TIMESTAMP在此处使用文字而不是TO_DATE()：
TRUNC(TIMESTAMP'2012-12-01 20:10:10', 'HH')
Second, and just to get this out of the way, are you storing dates or timestamps as strings? That's not a good idea.
其次，为了解决这个问题，您是否将日期或时间戳存储为字符串？这不是一个好主意。
Third, it's not a good idea to use BETWEENin date comparisons because you can miss the edge cases. It might be better to rewrite this as follows:
第三，BETWEEN在日期比较中使用不是一个好主意，因为您可能会错过边缘情况。将其重写如下可能会更好：
WHERE ( ResTRRequest.RequestTime >= TO_CHAR(TRUNC(TO_DATE('2012-12-01 20:10:10'), 'HH'), 'YYYY-MM-DD HH24:MI:SS')
AND ResTRRequest.RequestTime < TO_CHAR(TRUNC(CURRENT_TIMESTAMP, 'HH') + INTERVAL '1' HOUR, 'YYYY-MM-DD HH24:MI:SS') )
回答by Oleg Toporkov
Problem is in mask:
问题是在面具：
TO_DATE('2012-12-01 20:10:10', 'HH')
Replace with this one:
换成这个：
TO_DATE('2012-12-01 20:10:10', 'HH24')
回答by collapsar
Assuming that ResTRRequest.RequestTimeis of a date type, this Whereclause will work:
假设它ResTRRequest.RequestTime是日期类型，此Where子句将起作用：
where ResTRRequest.RequestTime
BETWEEN TRUNC(TO_DATE('2015-02-26 20:10:10', 'YYYY-MM-DD HH24:MI:SS'), 'HH')
AND TRUNC(CURRENT_TIMESTAMP, 'HH') + INTERVAL '59:59' MINUTE TO SECOND
If you have to compare character representations, keep in mind that you compare in lexicographic order, meaning that prefixes of strings are sorted before their strings! Avoid ensueing complications by using identical formatting models with componnents arranged in the order of decreasing significance. E.g.
如果您必须比较字符表示，请记住您按字典顺序进行比较，这意味着字符串的前缀在其字符串之前排序！通过使用相同的格式化模型，其组件按重要性递减的顺序排列，以避免随之而来的复杂性。例如
TO_CHAR(<whatever>, 'YYYY-MM-DD HH24:MI:SS')
but not
但不是
TO_CHAR(<whatever>, 'MM/DD/YYYY HH24:MI:SS')
回答by Marko Markovic
One more thing to watch out for in the case you get this error is the data itself. I've had date stored in xml tag that I had to parse and convert with TO_DATE with this format specifier 'MM/dd/YYYY HH:MI:SS AM'. SQL broke with "ORA-01849: hour must be between 1 and 12" because some records were written like this: "12/20/2017 16:45:00 PM". Pay attention to 16h and PM specfier...
如果出现此错误，还需要注意的另一件事是数据本身。我已经将日期存储在 xml 标记中，我必须使用此格式说明符“MM/dd/YYYY HH:MI:SS AM”解析和转换 TO_DATE。SQL 因“ORA-01849：小时必须介于 1 和 12 之间”而中断，因为有些记录是这样写的：“12/20/2017 16:45:00 PM”。注意16h和PM规范...
回答by Pramod
If the language setting on Oracle is set for using the 12 hours time, this problem will occur when converting the 24 hours time format. There are two solutions to this :
如果 Oracle 上的语言设置为使用 12 小时制，则在转换 24 小时制时间格式时会出现此问题。对此有两种解决方案：
Convert TIMESTAMP/DATE format in Oracle client
alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF6';
Convert query to match 24hr format
SELECT * FROM TEST_ WHERE DOB > TRUNC(TIMESTAMP'1970-01-01 20:10:10', 'HH'); or
SELECT * FROM TEST_ WHERE DOB > to_date('1970-01-01 20:00:00','YYYY-MM-DD HH24:MI:SS');
在 Oracle 客户端中转换 TIMESTAMP/DATE 格式
更改会话集 nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF6';
将查询转换为匹配 24 小时格式
SELECT * FROM TEST_ WHERE DOB > TRUNC(TIMESTAMP'1970-01-01 20:10:10', 'HH'); 或者
SELECT * FROM TEST_ WHERE DOB > to_date('1970-01-01 20:00:00','YYYY-MM-DD HH24:MI:SS');
