01843. 00000 - "not a valid month" [message #609618] |
Mon, 10 March 2014 07:10 |
|
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 |
|
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 #609640 is a reply to message #609628] |
Mon, 10 March 2014 12:36 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Littlefoot wrote on Mon, 10 March 2014 09:01Alternatively, you could addand 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 |
|
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 #609646 is a reply to message #609644] |
Mon, 10 March 2014 15:34 |
|
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
|
|
|
|