sql – ORA-01843 not a valid month- Comparing Dates

sql – ORA-01843 not a valid month- Comparing Dates

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);

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.
Its 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 youre using:

SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = TO_DATE(23/04/49,MM/DD/YY);

sql – ORA-01843 not a valid month- Comparing Dates

If you dont 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.