No question at this time
DBA Top 10
1 M. Cadot 15900
2 B. Vroman 15500
3 A. Kavsek 10000
4 T. Boles 6800
5 J. Schnackenberg 5800
6 M. Hidayathullah ... 3900
7 P. Wisse 1800
8 T. P 700
9 B. Derous 500
9 R. Wauben 500
9 H. Steijntjes 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48209
Total active users1645
Act. users last 24h6
Act. users last hour0
Registered user hits last week186
Registered user hits last month812
Go up

invalid month
Next thread: 12 c PDB CDB - Unit based pricing challenge
Prev thread: SLLoader direct path and unusable indices

Message Score Author Date
Dear All Below query runs ok with multilan char...... dattatraya walgude Mar 06, 2018, 14:41
<pre>SQL> select TO_DATE('06-OCT-2019','DD-MON-R...... Score: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 Pts Michel Cadot Mar 06, 2018, 14:50
Thanks Michel, Will check and update.... dattatraya walgude Mar 06, 2018, 14:52
Hi Michel, With 'YYYY' also it not work. As ...... dattatraya walgude Mar 06, 2018, 15:07
Hello, remarks: -a- good idea: use explicit fo...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman Mar 06, 2018, 15:17
>>> <i> With 'YYYY' also it not work. </i> ...... Michel Cadot Mar 06, 2018, 18:18
Thanks Michel & Bruno Still confuse :( sorry fo...... dattatraya walgude Mar 07, 2018, 04:04
The problem is NLS_DATE_<b>LANGUAGE</b> not NLS_...... Michel Cadot Mar 07, 2018, 06:09
Hello Dattatraya, -a- you might run the followi...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Bruno Vroman Mar 07, 2018, 08:16
Thanks Michel & Bruno, With the help of Oracle ...... dattatraya walgude Mar 09, 2018, 03:33
Hello Dattatraya, thanks for the feedback but I...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Bruno Vroman Mar 09, 2018, 08:26
Yes Sir I am 100% agree with you, also same thi...... dattatraya walgude Mar 09, 2018, 09:40
OMG: you can lead a horse to water, but you can't...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts JB Koyen Mar 21, 2018, 12:08

Follow up by mail Click here


Subject: invalid month
Author: dattatraya walgude, India
Date: Mar 06, 2018, 14:41, 107 days ago
Os info: any
Oracle info: 12c Standard Edition Release 12.1.0.2.0
Error info: ORA-01843: not a valid month
Message: Dear All

Below query runs ok with multilan charset database ( AL32UTF8) but giving error on WE8MSWIN1252.

SELECT COUNT(1) FROM DUAL WHERE ( TO_DATE('06-OCT-2019','DD-MON-RRRR') >TO_DATE('05-10-19','DD-MON-RRRR')) OR ( TO_DATE('06-OCT-2019','DD-MON-RRRR') >TO_DATE('05-OCT-2019','DD-MON-RRRR'))
*
ERROR at line 1:
ORA-01843: not a valid month

Please suggest.

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: invalid month
Author: Michel Cadot, France
Date: Mar 06, 2018, 14:50, 107 days ago
Score:   Score: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 Pts
Message:
SQL> select TO_DATE('06-OCT-2019','DD-MON-RRRR') from dual;

select TO_DATE('06-OCT-2019','DD-MON-RRRR') from dual
*
ERROR at line 1:
ORA-01843: not a valid month


SQL> select TO_DATE('06-OCT-2019','DD-MON-RRRR','NLS_DATE_LANGUAGE=AMERICAN') from dual;
TO_DATE('06-OCT-201
-------------------
06/10/2019 00:00:00

1 row selected.


And don't use format 'RRRR', "R" this was specifically introduced for Y2K problem and will just lead to problem in future.
The correct year format is 'YYYY'.

Regards
Michel

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: invalid month
Author: dattatraya walgude, India
Date: Mar 06, 2018, 14:52, 107 days ago
Message: Thanks Michel,

Will check and update.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: invalid month
Author: dattatraya walgude, India
Date: Mar 06, 2018, 15:07, 107 days ago
Message: Hi Michel,

With 'YYYY' also it not work.

As per primary analysis on one database ( AL32UTF8) query created like as below



SELECT COUNT(1) FROM DUAL WHERE ( TO_DATE('06-OCT-2019','DD-MON-RRRR')

>TO_DATE('05-10-19','DD-MON-RRRR')) OR ( TO_DATE('06-OCT-2019','DD-MON-RRRR')

>TO_DATE('05-OCT-2019','DD-MON-RRRR'))



On another database query generated as below ( WE8MSWIN1252)


SELECT COUNT(1) FROM DUAL WHERE ( TO_DATE('06-OCT-2019','DD-MON-RRRR')

>TO_DATE('05-OCT-19','DD-MON-RRRR')) OR ( TO_DATE('06-OCT-2019','DD-MON-RRRR')

>TO_DATE('05-OCT-2019','DD-MON-RRRR'))

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: invalid month
Author: Bruno Vroman, Belgium
Date: Mar 06, 2018, 15:17, 107 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello,

remarks:
-a- good idea: use explicit formats...
-b- but bad idea: provide a format that doesn't match the data... :-(

So from now on I assume that you will use a format that will match the data...
Still an additional remark:
-c- as shown by Michel: beware with a format element like "MON" as this is language dependent... So to be safe in this case you should also provide the language with the 'NLS_DATE_LANGUAGE=...' optional argument

-and a last one:
-d- when you have constant dates, a convenient (ISO) format is the keyword DATE followed by the date between single quotes and following the format YYYY-MM-DD, like for example:
  DATE '2018-02-28'
(so for example:
SELECT TO_CHAR( DATE '2018-02-28' + 6, 'FMDay DD mon YYYY', 'NLS_DATE_LANGUAGE=French' ) FROM dual;
)

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: invalid month
Author: Michel Cadot, France
Date: Mar 06, 2018, 18:18, 107 days ago
Message:

>>> With 'YYYY' also it not work.

Of course, this is not the problem, this was an additional advice for your code.

Also, the problem has nothing to do with the database character set.

The problem is that the CLIENT has not the correct default date language for the format you gave.
As I showed, if you specify this language in the SQL then it will work.

A better solution should be to NOT use month names but numbers; as numbers as the same ones for all languages countries (assuming the usage of Gregorian calendar), you will no more have any problem.

So in short, instead of:
TO_DATE('06-OCT-2019','DD-MON-RRRR')
use
TO_DATE('06-10-2019','DD-MM-YYYY')

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: invalid month
Author: dattatraya walgude, India
Date: Mar 07, 2018, 04:04, 106 days ago
Message: Thanks Michel & Bruno

Still confuse :( sorry for that..

When I ran select statement on "AMERICAN_AMERICA.WE8MSWIN1252" db it's give me "ORA-01843: not a valid month" , but it's ran successfully on " AMERICAN_AMERICA.AL32UTF8" .

SQL> l

1 select SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') DF
2 ,USERENV ('language') DL
3* from dual
SQL> /

DF DL
------------------------------ ------------------------------
DD-MON-RR AMERICAN_AMERICA.WE8MSWIN1252

SQL> SELECT COUNT(1)
FROM DUAL
WHERE ((TO_DATE('06-OCT-2019', 'DD-MON-RRRR') >
TO_DATE('05-10-19', 'DD-MON-RRRR'))
OR (TO_DATE('06-OCT-2019', 'DD-MON-RRRR') >
TO_DATE('05-OCT-2019', 'DD-MON-RRRR')))
/ 2 3 4 5 6 7
TO_DATE('05-10-19', 'DD-MON-RRRR'))
*
ERROR at line 4:
ORA-01843: not a valid month


SQL> l

1 select SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') DF
2 ,USERENV ('language') DL
3* from dual
SQL> /

DF DL
------------------------------ ----------------------------------------------------
DD-MON-RR AMERICAN_AMERICA.AL32UTF8

SQL> SELECT COUNT(1)
FROM DUAL
WHERE ((TO_DATE('06-OCT-2019', 'DD-MON-RRRR') >
TO_DATE('05-10-19', 'DD-MON-RRRR'))
OR (TO_DATE('06-OCT-2019', 'DD-MON-RRRR') >
TO_DATE('05-OCT-2019', 'DD-MON-RRRR')))
/ 2 3 4 5 6 7

COUNT(1)
----------
1


Implicit conversion not happen with ""AMERICAN_AMERICA.WE8MSWIN1252"

Regards
Dattatraya
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: invalid month
Author: Michel Cadot, France
Date: Mar 07, 2018, 06:09, 106 days ago
Message:
The problem is NLS_DATE_LANGUAGE not NLS_DATE_FORMAT.

In addition, depending on the COMPLETE version number, including patchset, "TO_DATE('05-10-19', 'DD-MON-RRRR')" will return an error.

Regards
Michel

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: invalid month
Author: Bruno Vroman, Belgium
Date: Mar 07, 2018, 08:16, 106 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hello Dattatraya,

-a- you might run the following in your two databases to reveal the differences:
    SELECT TO_CHAR( sysdate, 'FMDay DD mon YYYY' ) FROM dual;
(remark: the language can change at session level so you should never assume that is has a given value)

-b- when you do something like
    DATE('05-10-19', 'DD-MON-RRRR')
does the format match the data? NO
So don't count on chance: this is bad and you have to stop doing this if you don't want issues. In addition, Michel already advised you not to use RRRR but rather YYYY if you don't want issues.

So either you write things in a correct and safe way and everything will be fine, or you continue to complain "I do something wrong, sometimes it gives me a correct result and sometimes not, why doesn't it work each time?"

Best regards,

Bruno
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: invalid month
Author: dattatraya walgude, India
Date: Mar 09, 2018, 03:33, 104 days ago
Message: Thanks Michel & Bruno,

With the help of Oracle Support issue has been resolved.

Parameter "cursor_sharing" value is the cause.

 SQL> show parameter cursor_sharing;


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string FORCE
SQL> SELECT COUNT(1)
FROM DUAL
WHERE (TO_DATE('06-OCT-2019', 'DD-MON-RRRR') >
TO_DATE('05-10-19', 'DD-MON-RRRR'))
OR (TO_DATE('06-OCT-2019', 'DD-MON-RRRR') >
TO_DATE('05-OCT-2019', 'DD-MON-RRRR'))
/
TO_DATE('05-10-19', 'DD-MON-RRRR'))
*
ERROR at line 4:
ORA-01843: not a valid month


SQL> ALTER session set cursor_sharing ='EXACT';

Session altered.

SQL> show parameter cursor_sharing;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL> SELECT COUNT(1)
FROM DUAL
WHERE (TO_DATE('06-OCT-2019', 'DD-MON-RRRR') >
TO_DATE('05-10-19', 'DD-MON-RRRR'))
OR (TO_DATE('06-OCT-2019', 'DD-MON-RRRR') >
TO_DATE('05-OCT-2019', 'DD-MON-RRRR'))
/

COUNT(1)
----------
1

SQL>


Regards
Dattatraya
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: invalid month
Author: Bruno Vroman, Belgium
Date: Mar 09, 2018, 08:26, 104 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hello Dattatraya,

thanks for the feedback but I'm very sad that you insist on submitting bad statements...
Do you really want to rely on luck? Maybe after next patch this will not work anymore...

Isn't it better to simply submit correct SQL? Like for example
SELECT 'OK'

FROM DUAL
WHERE TO_DATE( '06-10-2019', 'DD-MM-YYYY') > TO_DATE( '10/05/2019', 'MM/DD/YYYY')
OR DATE '2019-10-06' > TO_DATE( '05-OCT-2019 07:33', 'DD-MON-YYYY HH24:MI', 'NLS_DATE_LANGUAGE=ENGLISH' )
Amazed regards,

Bruno
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: invalid month
Author: dattatraya walgude, India
Date: Mar 09, 2018, 09:40, 104 days ago
Message: Yes Sir

I am 100% agree with you, also same thing discussed with developer , but at the movement they are happy with solutions.


Actually, said query auto generated through one package, and they don't want to disturb that.


Regards
Dattatraya
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: invalid month
Author: JB Koyen, Belgium
Date: Mar 21, 2018, 12:08, 92 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: OMG:
you can lead a horse to water, but you can't make him drink
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here