ORA-01843 not a valid month- Comparing Dates
Solution 1
You should use the to_date
function (oracle/functions/to_date.php
)
SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = TO_DATE('23/04/49', 'DD/MM/YY');
Solution 2
You are comparing a date column to a string literal. In such a case, Oracle attempts to convert your literal to a date, using the default date format. It's a bad practice to rely on such a behavior, as this default may change if the DBA changes some configuration, Oracle breaks something in a future revision, etc.
Instead, you should always explicitly convert your literal to a date and state the format you're using:
SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = TO_DATE('23/04/49','MM/DD/YY');
Solution 3
If you don't need to check exact timestamp, use
SELECT * FROM MYTABLE WHERE trunc(DATEIN) = TO_DATE('23-04-49','DD-MM-YY');
otherwise, you can use
SELECT * FROM MYTABLE WHERE DATEIN = TO_DATE('23-04-49 20:18:07','DD-MM-YY HH24:MI:SS');
Here, you use hard code date,if you directly compare then you must use DD-MM-YY HH24:MI:SS else you might get ORA-01849: hour must be between 1 and 12.
Solution 4
I know this is a bit late, but I'm having a similar issue. SQL*Plus
executes the query successfully, but Oracle SQL Developer
shows the ORA-01843: not a valid month error.
SQL*Plus
seems to know that the date I'm using is in the valid format, whereas Oracle SQL Developer needs to be told explicitly what format my date is in.
SQL*Plus statement
:select count(*) from some_table where DATE_TIME_CREATED < '09-12-23';
VS
Oracle SQL Developer statement
:select count(*) from some_table where DATE_TIME_CREATED < TO_DATE('09-12-23','RR-MM-DD');
Solution 5
Just in case this helps, I solved this by checking the server date format:
SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT';
then by using the following comparison (the left field is a date+time):
AND EV_DTTM >= ('01-DEC-16')
I was trying this with TO_DATE
but kept getting an error. But when I matched my string with the NLS_DATE_FORMAT
and removed TO_DATE
, it worked...
Davidin073
Updated on August 04, 2021Comments
-
Davidin073 almost 3 years
I have a problem when try to select data from a table filtering by date.
For example:
SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = '23/04/49';
The Oracle Error is:
Informe de error: Error SQL: ORA-01843: mes no válido 01843. 00000 - "not a valid month" *Cause: *Action:
Probably the source data of table is corrupted, in this case:
- How can i solve this problem?
- Can I change this dates for null?
The results of this select,
select * from nls_session_parameters;
, is:PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_LANGUAGE SPANISH NLS_TERRITORY SPAIN NLS_CURRENCY ¿ NLS_ISO_CURRENCY SPAIN NLS_NUMERIC_CHARACTERS ,. NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD/MM/RR NLS_DATE_LANGUAGE SPANISH NLS_SORT SPANISH NLS_TIME_FORMAT HH24:MI:SSXFF NLS_TIMESTAMP_FORMAT DD/MM/RR HH24:MI:SSXFF NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR NLS_TIMESTAMP_TZ_FORMAT DD/MM/RR HH24:MI:SSXFF TZR NLS_DUAL_CURRENCY ¿ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE