使用add_months时在oracle中不是有效月份 [英] Not a valid month in oracle when add_months is used
问题描述
我在查询中给出了以下代码.
I have given the below code in query.
where to_date(cal_wid,'yyyymmdd')
between add_months(to_date(sysdate, 'MM-YYYY'),-12)
and to_date(sysdate, 'MM-YYYY')
我遇到以下错误. (我正在Xampp服务器上做
I am getting the following bug. (I am doing in a Xampp server)
Warning: oci_execute(): ORA-01843: not a valid month in C:\xampp\htdocs\xxx\index.php on line 149
Warning: oci_fetch_array(): ORA-24374: define not done before fetch or execute and fetch in C:\xampp\htdocs\xxx\index.php on line 160
谁能告诉我为什么我会收到此错误?
Can anyone tell why I am getting this error?
推荐答案
永远不要在已经为DATE
的对象上使用TO_DATE()
.这样做的原因是因为Oracle必须遵循您的意愿进行一些隐式转换:
Never, ever use TO_DATE()
on something that is already a DATE
. The reason for this is because Oracle will have to do some implicit conversions in order to follow your wishes:
TO_DATE(sysdate, 'mm-yyyy')
的运行方式实际上是
TO_DATE(TO_CHAR(sysdate, '<default nls_date_format parameter>'), 'mm-yyyy')
因此,如果您将nls_date_format设置为除'mm-yyyy'以外的其他值,您将会遇到问题.默认的nls_date_format参数为"DD-MON-YY",比您设置的值大得多.
so if your nls_date_format is set to something that's other than 'mm-yyyy', you're going to have problems. The default nls_date_format parameter is 'DD-MON-YY', which is more than likely the value yours is set to.
如果您要做的只是将add_months添加到当前月份的第一天,则应使用TRUNC()
,例如:
If all you wanted to do was to add_months to the 1st of the current month, then you should use TRUNC()
, eg:
add_months(trunc(sysdate, 'MM'),-12)
如果您按照Lalit的要求对已经是日期的日期进行日期化,则这里证明了隐式to_char-涉及to_date(sysdate)的基本查询的执行计划:
Here's proof of the implicit to_char if you to_date something that's already a date, as requested by Lalit - an execution plan of a basic query involving to_date(sysdate):
SQL_ID 3vs3gzyx2gtcn, child number 0
-------------------------------------
select * from dual where to_date(sysdate) < sysdate
Plan hash value: 3752461848
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE(TO_CHAR(SYSDATE@!))<SYSDATE@!)
您可以清楚地看到TO_CHAR()
在过滤条件下.
You can clearly see the TO_CHAR()
in the filter condition.
这篇关于使用add_months时在oracle中不是有效月份的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!