Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01839: date not valid for month specified (Oracle 10g)
ORA-01839: date not valid for month specified [message #600137] Fri, 01 November 2013 04:42 Go to next message
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 #600138 is a reply to message #600137] Fri, 01 November 2013 04:47 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You didn't look very carefully - November only has 30 days
Re: ORA-01839: date not valid for month specified [message #600141 is a reply to message #600138] Fri, 01 November 2013 05:15 Go to previous messageGo to next message
rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg
Senior Member
cookiemonster wrote on Fri, 01 November 2013 04:47
You 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.
Re: ORA-01839: date not valid for month specified [message #600143 is a reply to message #600141] Fri, 01 November 2013 05:28 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
that's why people invented exception handlers - it's easy enough using a PL/SQL function.
Re: ORA-01839: date not valid for month specified [message #600147 is a reply to message #600141] Fri, 01 November 2013 05:52 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
rc3d wrote on Fri, 01 November 2013 10:15
cookiemonster wrote on Fri, 01 November 2013 04:47
You 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!
Re: ORA-01839: date not valid for month specified [message #600153 is a reply to message #600143] Fri, 01 November 2013 07:32 Go to previous messageGo to next message
rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg
Senior Member
cookiemonster wrote on Fri, 01 November 2013 05:28
that's why people invented exception handlers - it's easy enough using a PL/SQL function.


am I right? In pure SQL this not possible?

SELECT    'UPDATE user_jc WITH user_id = "'
       || USER_ID
       || '" , jc_name = "'
       || JC_NAME
       || '" SET  __99__DateTo = "";'
  FROM XXX_USER_JC_ORDERID
 WHERE DATETO IS NOT NULL
       AND TO_DATE (DATETO, 'DD.MM.YYYY') > ADD_MONTHS (SYSDATE, 120);


this code will break as soon someone inserts wrong date.
Re: ORA-01839: date not valid for month specified [message #600154 is a reply to message #600153] Fri, 01 November 2013 07:39 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's possible, but it's simpler with a function.
You could write a case statement to check that DD is in the correct range for MM.
Re: ORA-01839: date not valid for month specified [message #600155 is a reply to message #600154] Fri, 01 November 2013 07:39 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
you would have to remember to account for leap-years
Re: ORA-01839: date not valid for month specified [message #600588 is a reply to message #600155] Fri, 08 November 2013 14:42 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
make it a date field. Then they can't ever enter invalid data. And it does it's own validation.

[Updated on: Fri, 08 November 2013 14:43]

Report message to a moderator

Re: ORA-01839: date not valid for month specified [message #600590 is a reply to message #600588] Fri, 08 November 2013 14:55 Go to previous messageGo to next message
rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg
Senior Member
Bill B wrote on Fri, 08 November 2013 14:42
make it a date field. Then they can't ever enter invalid data. And it does it's own validation.


not possible. It's a Commercial off-the-shelf product.
Re: ORA-01839: date not valid for month specified [message #600591 is a reply to message #600590] Fri, 08 November 2013 15:17 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
You can still validate with a trigger which will validate or rewrite the date string so it is always correct. You can leave the column blank if it can't be decoded. validate date ranges. all kind of good stuff.
Previous Topic: Record vs Object problem
Next Topic: Select distinct
Goto Forum:
  


Current Time: Fri Mar 29 01:08:33 CDT 2024