Today I was trying to run this very basic query. When I ran the query I got the following error.
>[Error] Script lines: 1-4 -------------------------- ORA-01843: not a valid monthFor whatever reason it did not like the month. A little hunting around and I found some information about date formats in Oracle. Unless you set some type of client environment variable it will run off of the database default. If you need to find out what that is you can run the following sql. Looking at the session parameters I was able to identify that the default NLS_DATE_FORMAT was set to 'DD-MON-RR'. As I said before, I think you can change this in your environment but I just updated my query. If anyone has something to add to this please feel free, I am a complete newb when it comes to Oracle.
#1 by zac spitzer on 1/28/10 - 8:24 PM
#2 by James Medlin on 1/31/10 - 9:30 AM
I usually use Oracle's to_date function to specify the format myself. That way my code doesn't depend on the setup of the Oracle database. An example:
select *
from MYTABLE
where REQUESTDATE >= to_date('12/1/2009', 'mm/dd/yyyy')
#3 by Dan Vega on 2/4/10 - 2:38 PM
I have done that before as well. I was not aware of the issues that might come up later using the method I did. Thanks for the tip James!