ORACLE-ORA-01843:无效月份 [英] ORACLE - ORA-01843: not a valid month

问题描述

我更改了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

我收到此消息:

消息:oci_execute():ORA-01843:无效的月份

它与Toad for Oracle 11一起使用.您有任何解决方案吗?

谢谢:)

解决方案

假定OD.DATE2具有DATE数据类型，则需要将date-as-strings(例如'08/03/2015')显式转换为日期格式. /p>

类似的东西:

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;

请注意，我是如何从选择列表和分组列表中的OD.DATE2周围删除to_date的，因为对已存在的内容使用to_date是非常的坏主意日期.

这样做，您迫使Oracle将隐式转换为字符串，这将通过使用NLS_DATE_FORMAT参数来决定将字符串输出为哪种格式，然后再尝试将其转换回日期使用您指定的格式掩码，如下所示:

to_date(to_char(od.date2, '<nls_date_format parameter>'), 'DD/MM/YYYY')

如果两种格式的掩码不同，则会遇到错误……例如提示您发布此问题的掩码！

I change OCI8 version for PHP and since this query dosn't work :

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

It works with Toad for Oracle 11. Do you have any solution ?

Thank you :)

解决方案

Assuming that OD.DATE2 is of DATE datatype, you need to explicitly convert the dates-as-strings (eg. '08/03/2015') into date format.

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_date from around OD.DATE2 in the select and group by lists, since it is a very bad idea to use to_date against something that is already a 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:

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!

这篇关于ORACLE-ORA-01843:无效月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！