How to solve 'ORA-01843 not a valid month'?

Question

I am using Oracle 11g and while trying to run Select query with a condition on column of type Timestamp, the following error occurs.

ORA-01843: not a valid month
01843. 00000 -  "not a valid month"
*Cause:    
*Action:

My query is similar to this -

SELECT * FROM TABLE T WHERE T.LAST_ACCESS_DATE  < '2016/01/30';

Is there any function to properly fomat the Date or is there any particular pattern in which Date should be passed?

Answer 1

This problem occurs because the date in the condition is ot in proper format.
This has two possible solutions -

Solution 1

Use Valid values for months and use dd/mm/yyyy format .Valid values for month are:

• January or Jan
• February or Feb
• March or Mar
• April or Apr
• May or May
• June or Jun
• July or Jul
• August or Aug
• September or Sep
• October or Oct
• November or Nov
• December or Dec

Example -

SELECT * FROM TABLE T WHERE T.LAST_ACCESS_DATE  < '30/Jan/2016';

Solution 2

Use TO_DATE function to properly format date-

Example -

SELECT * FROM TABLE T WHERE T.LAST_ACCESS_DATE  < TO_DATE('30/01/16', 'DD/MM/YY');