Home » SQL & PL/SQL » SQL & PL/SQL » 01843. 00000 - "not a valid month" (sql developer, 11g)
01843. 00000 - "not a valid month" [message #609618] Mon, 10 March 2014 07:10 Go to next message
meeplsql
Messages: 5
Registered: February 2014
Location: bangalore
Junior Member
releasedate(varchar2) values
140101
131225
131213
131213
131201
131129
120812
120812
120812
000000
000000


I want in date format date between 131101 to 131218

select releasedate from SERVORDER where TO_DATE(RELEASEDATE,'RRMMDD')
BETWEEN TO_DATE('131101','RRMMDD')
AND TO_DATE('131218','RRMMDD')

this gives error
ORA-01843: not a valid month
01843. 00000 - "not a valid month"


Re: 01843. 00000 - "not a valid month" [message #609619 is a reply to message #609618] Mon, 10 March 2014 07:18 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
A valid month is between 01 and 12. The last two lines in your sample data (two digits of the month in the middle) is 00 which is not a valid month.
Quote:

releasedate(varchar2) values
140101
131225
131213
131213
131201
131129
120812
120812
120812
000000
000000


In addition if the time portion is not relevant in your query, instead of using to_date you can use simply a date literal. For example date '2014-03-14'.

[Updated on: Mon, 10 March 2014 07:23]

Report message to a moderator

Re: 01843. 00000 - "not a valid month" [message #609620 is a reply to message #609619] Mon, 10 March 2014 07:27 Go to previous messageGo to next message
meeplsql
Messages: 5
Registered: February 2014
Location: bangalore
Junior Member
I cant give full year (yyyy) because data has only 2 digit year
Re: 01843. 00000 - "not a valid month" [message #609621 is a reply to message #609620] Mon, 10 March 2014 07:28 Go to previous messageGo to next message
meeplsql
Messages: 5
Registered: February 2014
Location: bangalore
Junior Member
I know in my data 00000 is there but how to overcome this
Re: 01843. 00000 - "not a valid month" [message #609624 is a reply to message #609620] Mon, 10 March 2014 07:55 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
meeplsql wrote on Mon, 10 March 2014 13:27
I cant give full year (yyyy) because data has only 2 digit year


I didn't talk about the year but month, I said the two digits in the middle.
Re: 01843. 00000 - "not a valid month" [message #609625 is a reply to message #609621] Mon, 10 March 2014 07:57 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
meeplsql wrote on Mon, 10 March 2014 13:28
I know in my data 00000 is there but how to overcome this

A better question: how did 000000 get there? An invalid value in a column supposed to hold date values?
Re: 01843. 00000 - "not a valid month" [message #609626 is a reply to message #609621] Mon, 10 March 2014 07:57 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
hi,

Please follow the guidelines for getting solution as per given by ORA FAQ.Please use the code tag and always provide create table/insert table scripts
for getting faster solutions.

Please find solution for your request.



select       * 
from         servorder 
where        trunc(TO_DATE(DECODE(releasedate,'000000',NULL,releasedate),'YYMMDD'))
   BETWEEN   trunc(TO_DATE('131101','YYMMDD')) AND trunc(TO_DATE('131218','YYMMDD'))

Re: 01843. 00000 - "not a valid month" [message #609628 is a reply to message #609626] Mon, 10 March 2014 08:01 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Alternatively, you could add
and releasedate <> '000000'
into the WHERE clause.
Re: 01843. 00000 - "not a valid month" [message #609629 is a reply to message #609628] Mon, 10 March 2014 08:06 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Also another good question: Why store date values as varchar2 values whereas the DATE data type is available for that? Smile

[Updated on: Mon, 10 March 2014 08:09]

Report message to a moderator

Re: 01843. 00000 - "not a valid month" [message #609640 is a reply to message #609628] Mon, 10 March 2014 12:36 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Littlefoot wrote on Mon, 10 March 2014 09:01
Alternatively, you could add
and releasedate <> '000000'
into the WHERE clause.


And that would be a ticking bomb. There is no predicate execution order so even though it is highly unlikely but it is possible optimizer will decide to check to_date(releasedate,'RRMMDD') between to_date('131101','RRMMDD') and to_date('131218','RRMMDD') first.

SY.
Re: 01843. 00000 - "not a valid month" [message #609641 is a reply to message #609640] Mon, 10 March 2014 12:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
WITH reject0 
     AS (SELECT * 
         FROM   servorder 
         WHERE  releasedate <> '000000') 
SELECT releasedate 
FROM   servorder so , REJECT0 R0
WHERE  SO.id = r0.id 
       AND To_date(releasedate, 'RRMMDD') BETWEEN 
           To_date('131101', 'RRMMDD') AND To_date( 
           '131218', 'RRMMDD') 

/ 

assuming that some UNIQUE row identifier exists like "ID"

[Updated on: Mon, 10 March 2014 12:57]

Report message to a moderator

Re: 01843. 00000 - "not a valid month" [message #609644 is a reply to message #609641] Mon, 10 March 2014 15:09 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Again, no guarantee SO.id = r0.id will be applied first.

SY.
Re: 01843. 00000 - "not a valid month" [message #609646 is a reply to message #609644] Mon, 10 March 2014 15:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
WITH reject0 
     AS (SELECT * 
         FROM   servorder 
         WHERE  releasedate <> '000000'
           and rownum > 0) 
SELECT releasedate 
FROM   REJECT0
WHERE To_date(releasedate, 'RRMMDD') BETWEEN To_date('131101', 'RRMMDD') AND To_date('131218', 'RRMMDD') 
/


"rownum > 0" forces the materialization (if you don't want to use the "materialize" hint).

[Updated on: Mon, 10 March 2014 15:36]

Report message to a moderator

Re: 01843. 00000 - "not a valid month" [message #609647 is a reply to message #609618] Mon, 10 March 2014 18:11 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
SELECT  releasedate 
  FROM  servorder
  WHERE To_date(nullif(releasedate,'000000'),'RRMMDD') BETWEEN To_date('131101','RRMMDD') AND To_date('131218','RRMMDD') 
/


SY.
Previous Topic: Generating Random numbers query
Next Topic: Excute procedure
Goto Forum:
  


Current Time: Fri Mar 29 05:55:11 CDT 2024