Oracle Date Format Issue

I am using an old software and it is running on Oracle 8i.

Trying to retrieve any result from the database but have been unsuccessful so far. Any help would be appreciated. The dates are coming from a web form.

ALTER SESSION SET NLS_DATE_FORMAT='Month.DD.YYYY';

SELECT * FROM AuditTrail WHERE DateAttempted>=TO_DATE('July 29, 2013','Month.DD.YYYY') AND DateAttempted<=TO_DATE('October 29, 2013','Month.DD.YYYY') ORDER BY AuditNo
LVL 1
mathew_sAsked:
Who is Participating?
 
mathew_sConnect With a Mentor Author Commented:
Got it to work, had to set  NLS_DATE_FORMAT to the following, not sure why it works but it does.

ALTER SESSION SET NLS_DATE_FORMAT = 'MM-DD-YYYY HH:MI:SSAM';

SELECT * FROM AuditTrail WHERE DateAttempted>=TO_DATE('July 29, 2013','Month DD, YYYY') AND DateAttempted<=TO_DATE('October 29, 2013','Month DD, YYYY') ORDER BY AuditNo;
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
The string must match the format mask:

TO_DATE('July 29, 2013','Month DD, YYYY')
0
 
mathew_sAuthor Commented:
I get the following error: ORA-01843: not a valid month.

I also tried just running the second statement and get the same error.

ALTER SESSION SET NLS_DATE_FORMAT='Month DD, YYYY';

SELECT * FROM AuditTrail WHERE DateAttempted>=TO_DATE('July 29, 2013','Month DD, YYYY') AND DateAttempted<=TO_DATE('October 29, 2013','Month DD, YYYY') ORDER BY AuditNo;
0
 
slightwv (䄆 Netminder) Commented:
NLS_DATE_FORMAT is used to determine the format when Oracle needs to do an implicit data conversion.

When just selecting data I do not see where you would get the "ORA-01843: not a valid month" error.
0
 
mathew_sAuthor Commented:
I believe both NLS_DATE_FORMAT and format mask were issues so points split.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.