SQL ORA-01843 不是有效月份 - 比较日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21157224/
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-01843 not a valid month- Comparing Dates
提问by Davidin073
I have a problem when try to select data from a table filtering by date.
尝试从按日期过滤的表中选择数据时遇到问题。
For example:
例如：
SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = '23/04/49';
SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = '23/04/49';
The Oracle Error is:
甲骨文错误是：
Informe de error: Error SQL: ORA-01843: mes no válido 01843. 00000 - "not a valid month" *Cause: *Action:
Informe de error: Error SQL: ORA-01843: mes no válido 01843. 00000 - "not a valid month" *Cause: *Action:
Probably the source data of table is corrupted, in this case:
可能表的源数据已损坏，在这种情况下：
- How can i solve this problem?
- Can I change this dates for null?
- 我怎么解决这个问题？
- 我可以将此日期更改为空吗？
The results of this select, select * from nls_session_parameters;, is:
这个选择的结果select * from nls_session_parameters;是：
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE SPANISH
NLS_TERRITORY SPAIN
NLS_CURRENCY ?
NLS_ISO_CURRENCY SPAIN
NLS_NUMERIC_CHARACTERS ,.
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD/MM/RR
NLS_DATE_LANGUAGE SPANISH
NLS_SORT SPANISH
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY ?
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
回答by Thomas B. Lze
You should use the to_datefunction (oracle/functions/to_date.php
)
您应该使用该to_date函数（oracle/functions/to_date.php
）
SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = TO_DATE('23/04/49', 'DD/MM/YY');
回答by Mureinik
You are comparing a date column to a string literal. In such a case, Oracle attempts to convert your literal to a date, using the default date format. It's a bad practice to rely on such a behavior, as this default may change if the DBA changes some configuration, Oracle breaks something in a future revision, etc.
您正在将日期列与字符串文字进行比较。在这种情况下，Oracle 会尝试使用默认日期格式将您的文字转换为日期。依赖这种行为是一种不好的做法，因为如果 DBA 更改某些配置、Oracle 在未来修订中破坏某些内容等，则此默认值可能会更改。
Instead, you should always explicitly convert your literal to a date and state the format you're using:
相反，您应该始终将文字显式转换为日期并说明您使用的格式：
SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = TO_DATE('23/04/49','MM/DD/YY');
回答by Piyushkumar Kachhadiya
If you don't need to check exact timestamp, use
如果您不需要检查确切的时间戳，请使用
SELECT * FROM MYTABLE WHERE trunc(DATEIN) = TO_DATE('23-04-49','DD-MM-YY');
otherwise, you can use
否则，您可以使用
SELECT * FROM MYTABLE WHERE DATEIN = TO_DATE('23-04-49 20:18:07','DD-MM-YY HH24:MI:SS');
Here, you use hard code date,if you directly compare then you must use DD-MM-YY HH24:MI:SS else you might get ORA-01849: hour must be between 1 and 12.
在这里，您使用硬编码日期，如果您直接比较，那么您必须使用 DD-MM-YY HH24:MI:SS 否则您可能会得到 ORA-01849：小时必须介于 1 和 12 之间。
回答by Octavian C.
I know this is a bit late, but I'm having a similar issue. SQL*Plusexecutes the query successfully, but Oracle SQL Developershows the ORA-01843: not a valid month error.
我知道这有点晚了，但我遇到了类似的问题。SQL*Plus成功执行查询，但Oracle SQL Developer显示ORA-01843: not a valid month error.
SQL*Plusseems to know that the date I'm using is in the valid format, whereas Oracle SQL Developer needs to be told explicitly what format my date is in.
SQL*Plus似乎知道我使用的日期格式有效，而 Oracle SQL Developer 需要明确告知我的日期格式。
SQL*Plus statement:select count(*) from some_table where DATE_TIME_CREATED < '09-12-23';
SQL*Plus statement：select count(*) from some_table where DATE_TIME_CREATED < '09-12-23';
VS
VS
Oracle SQL Developer statement:select count(*) from some_table where DATE_TIME_CREATED < TO_DATE('09-12-23','RR-MM-DD');
Oracle SQL Developer statement：select count(*) from some_table where DATE_TIME_CREATED < TO_DATE('09-12-23','RR-MM-DD');
回答by Devi Pāduka
Just in case this helps, I solved this by checking the server date format:
以防万一这有帮助，我通过检查服务器日期格式解决了这个问题：
SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT';
then by using the following comparison (the left field is a date+time):
然后通过使用以下比较（左侧字段是日期+时间）：
AND EV_DTTM >= ('01-DEC-16')
I was trying this with TO_DATEbut kept getting an error. But when I matched my string with the NLS_DATE_FORMATand removed TO_DATE, it worked...
我正在尝试此操作，TO_DATE但一直出现错误。但是当我将我的字符串与NLS_DATE_FORMAT和删除匹配时TO_DATE，它起作用了......
回答by Rosmery Acevedo
ALTER session set NLS_LANGUAGE='AMERICAN';
ALTER 会话集 NLS_LANGUAGE='AMERICAN';
回答by Thorsten Kettner
In a comment to one of the answers you mention that to_date with a format doesn't help. In another comment you explain that the table is accessed via DBLINK.
在对其中一个答案的评论中，您提到带有格式的 to_date 无济于事。在另一条评论中，您解释说该表是通过 DBLINK 访问的。
So obviously the other system contains an invalid date that Oracle cannot accept. Fix this in the other dbms (or whatever you dblink to) and your query will work.
所以很明显，另一个系统包含一个 Oracle 不能接受的无效日期。在其他 dbms（或 dblink 到的任何内容）中修复此问题，您的查询将起作用。
Having said this, I agree with the others: always use to_date with a format to convert a string literal to a date. Also never use only two digits for a year. For example '23/04/49' means 2049 in your system (format RR), but it confuses the reader (as you see from the answers suggesting a format with YY).
话虽如此，我同意其他人的看法：始终使用带有格式的 to_date 将字符串文字转换为日期。也永远不要在一年中只使用两位数。例如，“23/04/49”在您的系统中表示 2049（格式为 RR），但它会使读者感到困惑（正如您从建议使用 YY 格式的答案中看到的那样）。
回答by Naga
If the source date contains minutes and seconds part, your date comparison will fail. you need to convert source date to the required format using to_char and the target date also.
如果源日期包含分钟和秒部分，您的日期比较将失败。您还需要使用 to_char 和目标日期将源日期转换为所需的格式。
