ORACLE - ORA-01843: 无效月份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35869479/
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
ORACLE - ORA-01843: not a valid month
提问by Solid-Snake
I change OCI8 version for PHP and since this query dosn't work :
我更改了 PHP 的 OCI8 版本，因为此查询不起作用：
SELECT 'M'||to_char(to_date(OD.DATE2,'DD/MM/YYYY'),'MM') PERIODE, count(*) DATA, OD.DCCPT DCCPT
FROM BDD OD
WHERE BDD = 'phone'
AND OD.SENS = 'Ent'
AND OD.DCCPT IN('PIOLUC')
AND (OD.DATE2 BETWEEN '08/03/2015' AND '08/03/2016')
group by 'M'||to_char(to_date(OD.DATE2,'DD/MM/YYYY'),'MM'), OD.CDCCPT CDCCPT
I got this message :
我收到这条消息：
Message: oci_execute(): ORA-01843: not a valid month
消息：oci_execute()：ORA-01843：无效月份
It works with Toad for Oracle 11. Do you have any solution ?
它适用于 Oracle 11 的 Toad。您有任何解决方案吗？
Thank you :)
谢谢 ：）
回答by MT0
Looking at the line:
看线：
OD.DATE2 BETWEEN '08/03/2015' AND '08/03/2016'
Then '08/03/2015'and '08/03/2016'are string literals and are not dates.
然后'08/03/2015'和'08/03/2016'是字符串文字而不是日期。
Oracle will attempt an implicit conversion from a string literal to a date using your NLS_DATE_FORMATsession parameter as the format mask and if this does not work it will throw an error.
Oracle 将尝试使用您的NLS_DATE_FORMAT会话参数作为格式掩码从字符串文字到日期的隐式转换，如果这不起作用，它将引发错误。
The simple solution is not to use string literals but use date literals instead:
简单的解决方案是不使用字符串文字，而是使用日期文字：
SELECT 'M'||to_char( DATE2, 'MM' ) PERIODE,
count(*) DATA,
DCCPT
FROM BDD
WHERE BDD = 'phone'
AND SENS = 'Ent'
AND DCCPT IN ( 'PIOLUC' )
AND DATE2 BETWEEN DATE '2015-03-08' AND DATE '2016-03-08'
GROUP BY
'M'||to_char( DATE2, 'MM' ),
DCCPT
But you could also specify the format mask:
但您也可以指定格式掩码：
OD.DATE2 BETWEEN TO_DATE( '08/03/2015', 'DD/MM/YYYY' ) AND TO_DATE( '08/03/2016', 'DD/MM/YYYY' )
回答by Boneist
Assuming that OD.DATE2is of DATE datatype, you need to explicitly convert the dates-as-strings (eg. '08/03/2015') into date format.
假设它OD.DATE2是 DATE 数据类型，您需要将日期作为字符串（例如'08/03/2015'）显式转换为日期格式。
Something like:
就像是：
SELECT 'M'||to_char(OD.DATE2,'MM') PERIODE,
count(*) DATA,
OD.DCCPT DCCPT
FROM BDD OD
WHERE BDD = 'phone'
AND OD.SENS = 'Ent'
AND OD.DCCPT IN ('PIOLUC')
AND OD.DATE2 BETWEEN to_date('08/03/2015', 'dd/mm/yyyy') AND to_date('08/03/2016', 'dd/mm/yyyy')
group by 'M'||to_char(OD.DATE2,'MM'),
OD.DCCPT;
Note how I have removed the to_datefrom around OD.DATE2in the select and group by lists, since it is a verybad idea to use to_dateagainst something that is already a DATE.
请注意我是如何在 select 和 group by 列表中删除to_datefrom aroundOD.DATE2的，因为对已经是 DATE 的内容使用是一个非常糟糕的主意to_date。
By doing so, you force Oracle to do an implicit conversion to a string, which it will do so by using your NLS_DATE_FORMAT parameter to decide what format to output the string as, before it then tries to convert it back to a date using the format mask you specified, like so:
通过这样做，您可以强制 Oracle 对字符串进行隐式转换，它将通过使用您的 NLS_DATE_FORMAT 参数来决定输出字符串的格式，然后再尝试使用该格式将其转换回日期您指定的掩码，如下所示：
to_date(to_char(od.date2, '<nls_date_format parameter>'), 'DD/MM/YYYY')
If the two format masks aren't the same, you will run into errors... such as the one that prompted you to post this question!
如果两个格式掩码不一样，你会遇到错误......比如那个提示你发布这个问题的错误！
