danvega.org: Oracle default Date Format

Oracle default Date Format

Thursday January 28, 2010 1:45 PM

Word Count: 275

Today I was trying to run this very basic query. select * from MYTABLE where REQUESTDATE >= '12/1/2009' AND REQUESTDATE <= '12/1/2009' order by REQUESTDATE When I ran the query I got the following error.

>[Error] Script lines: 1-4 -------------------------- ORA-01843: not a valid month

For 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.

422 Views

Comments

#1 Posted By: zac spitzer Posted On: 1/28/10 8:24 PM

if you use cfqueryparam, you'll be passing in a date object which would be parsed according to your CF locale?

#2 Posted By: James Medlin Posted On: 1/31/10 9:30 AM

One downside to hardcoding the date format like that is if you move your code to a different Oracle database or if the NLS_DATE_FORMAT parameter gets changed then you me looking at a lot of code changes.
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 Posted By: Dan Vega Posted On: 2/4/10 2:38 PM |

Author Comment

Zac - I was working off of straight sql here, no cf.

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!

Oracle default Date Format

Comments

Post Your Comment

Navigation

Tagcloud

Archives

Blogs I Read

Recent Entries

Recent Comments

About

Stay Upated