| |
In the morning, I received a message from a developer that said SQL was being executed in a test environment, but the same SQL was executed in another test environment, so I could help analyze the reason. SQL being given as follows:
SQL> select to_date (substr (cvalidate, 6, 10), 'MM-DD') from lcpol where polno = '210010000123931';
Select to_date (substr (cvalidate, 6, 10), 'MM-DD') from lcpol where polno = '210010000123931';
*
ERROR at line 1:
ORA-01839: date not valid for month specified
Look at the error message is ORA-01839: date not valid for month specified. First thought may be a data problem caused by the use of TO_DATE error. In any case to go to verify the next is not really the existence of the error. First connected to the implementation of the error that the library, the implementation of SQL, and sure enough, as the developer said being given. Then connected to the implementation of the normal reservoir, the implementation of SQL, and sure enough, Oracle is very friendly to return the results. Since the wrong facts exist, it is necessary to find out the cause of the error.
According to the first response to the error, go ahead and compare the data on both sides is the same. The results were a bit disappointing, and the data on both sides was exactly the same. SQL itself is a problem? Careful analysis of the next SQL, it should be true ah, although the use of 'MM-DD' formatting not very good, but it should not lead to the implementation of the wrong ah Moreover, the other is the implementation of the correct reservoir. Where is the problem in the end? Is the date of the two libraries set up different parameters? Look at the two databases NLS_CHARACTERSET,
NLS_NCHAR_CHARACTERSET, NLS_DATE_LANGUAGE, NLS_DATE_FORMAT settings are the same. Well, it seems that the problem should not be in the data.
Looking back, in the conditions polno = '210010000123931' under the cvalidate is what value?
SQL> select cvalidate from lcpol where polno = '210010000123931'; CVALIDATE ---------- 2012-02-29
It seems that the value is also quite special, 4 years will appear once. But special to special, should not be given the. Think directly if the value of what will happen? Well, try it out:
SQL> select to_date (substr ( '2012-02-29', 6, 10), 'MM-DD') from dual;
ERROR
-----------------------
ERROR at line 1:
ORA-01839: date not valid for month specified
SQL> select to_date (substr ( '2012-02-29', 6, 10), 'MM-DD') from dual;
TO_DATE (SU
----------
2012-02-29
Test results are the same. Is suddenly unable to do anything, suddenly noticed that although the use of 'MM-DD' to format the date, but the results still have years of information! Think of the current NLS_DATE_FORMAT as 'YYYY-MM-DD', guess Oracle will use the current system date to attach the information of the year. Is that the implementation of the wrong date of the reservoir system is not correct, immediately look at, and sure enough, the system date is 2013!
$ Date
Fri Mar 1 18:45:13 CST 2013
Contact your system management team to fix the system date. After correcting the system date, the SQL execution is normal and the problem is solved. |
|
|
|