Message: ORA-01843: not a valid month

Link... on the 3rd day of April, 2013

This stuff makes me confuse and headache for a few days. Why? It is about date format in select query oracle. I don’t know if this has been an issue. I have select SQL query in ORACLE that contains date format, I use to_date(targetdate, ‘DD-MON-YY’) format. Every thing works well in development server that has database connection string to database development server using oracle, but when I change connection to database production server, some thing happens. No data appear and no error, a mystery. Why?

I need more than a hours to find the solutions. Every thing has been done, debug from visual studio and makes connection directly to production server, no error found and the data appear, copy database production and restore to development server, no error found and the data appear. But if I make connection directly from development server to database production server, no error but no data appear.

Finally, I try to check event log in windows event viewer, fortunately we have custom event viewer for application. The problems found. Every runs application, custom event log added. It is about “Message: ORA-01843: not a valid month” and quickly find date format and query select. Date uses  to_date(targetdate, ‘DD-MON-YY’) and then I try to remove date format and run application, every things go well. But I need format date for comparing date, and then I change to use format to_char(targetdate,’DD-MM-YYYY‘).

This is to be an issue I think and be careful if using date format  to_date(targetdate, ‘DD-MON-YY’) in sql query. It works fine if application connects to oracle 10g but if fails if application connects to oracle 11g. Oh ya, my development server uses 10g and production 11g. I am still looking for why this is so.

Reference to know:

  1. http://www.dbforums.com/oracle/1215287-ora-01843-not-valid-month-not-all-should.html
  2. http://www.experts-exchange.com/Programming/Languages/.NET/Visual_CSharp/Q_22634221.html