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

+1 vote
4,592 views
asked Jan 30, 2016 by Rahul Singh (163 points)  

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?

1 Answer

0 votes
answered Feb 22, 2016 by Hitesh Garg (135 points)  
selected Apr 1, 2016 by Hitesh Garg
 
Best answer

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