ORA-01839: date not valid for month specified [message #600137] |
Fri, 01 November 2013 04:42 |
|
rc3d
Messages: 213 Registered: September 2013 Location: Baden-Württemberg
|
Senior Member |
|
|
Hi
our web application has a field DATETO which has no validation (end date). So user can put garbage there. In this field should be SYSDATE + max. 2 years. But also SYSDATE + 1 month OK.
I need to make a script that sets for example "31.12.9999" to null. Because so long end date is non sense.
SQL> SELECT EXTRACT (YEAR FROM (TO_DATE (DATETO, 'DD.MM.YYYY')))
FROM XXX_USER_JC_ORDERID
WHERE DATETO IS NOT NULL;
ERROR:
ORA-01839: date not valid for month specified
no rows selected
Littlefoot writes about ORA-01839 "It usually happens when invalid dates are used, such as 30th of February"
http://www.orafaq.com/forum/t/127119/0/
I don't see invalid date???
SQL> SELECT DATETO
FROM XXX_USER_JC_ORDERID
WHERE DATETO IS NOT NULL;
DATETO
--------------------------------------------------------------------------------
23.09.2013
31.12.2013
31.11.2013
31.12.2018
31.12.9999
16.07.2015
31.12.2016
31.12.2014
31.12.2014
13.07.2013
31.12.2013
DATETO
--------------------------------------------------------------------------------
31.12.2014
31.12.2016
31.12.2013
31.08.2014
31.12.2023
31.12.9999
31.12.2014
21.08.2013
19 rows selected.
SQL> desc XXX_USER_JC_ORDERID;
Name Null? Type
----------------------------------------- -------- ----------------------------
USER_ID NOT NULL VARCHAR2(20 CHAR)
JC_NAME NOT NULL VARCHAR2(25 CHAR)
UPD_TIME DATE
ORDERID VARCHAR2(4000)
BIZ VARCHAR2(4000)
DATETO VARCHAR2(4000)
|
|
|
|
|
|
Re: ORA-01839: date not valid for month specified [message #600147 is a reply to message #600141] |
Fri, 01 November 2013 05:52 |
John Watson
Messages: 8922 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
rc3d wrote on Fri, 01 November 2013 10:15cookiemonster wrote on Fri, 01 November 2013 04:47You didn't look very carefully - November only has 30 days
thanks. As I understand my script is mission impossible. As soon as somebody inserts 31.11.2013 my script breaks...
I have no control what people inserts.
This sort of problem can be very interesting to fix. Your code has to catch every possible kind of user error. I had to deal with this once, when uploading data from an Excel spreadsheet saved as a CSV file. It was awful. Typical data was:
05/01/53
1-5-2005
12 sep 13
O1/01/2013 and so on. We wrote pattern-recognizing code that parsed everything that could be interpreted as an unambiguous date into a date (catching upper case O and changing it to a zero was a nice one, also various spelling mistakes in month names), and rejected the ambiguous dates and the impossible dates. I can't post the code here because it is proprietary, but I can warn you that you need to think of every possible stupid thing that you might be faced with. Have fun!
|
|
|
|
|
|
|
|
|