asked on

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

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mathew_s

ASKER

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;

ASKER CERTIFIED SOLUTION

mathew_s

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

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.

mathew_s

ASKER

I believe both NLS_DATE_FORMAT and format mask were issues so points split.