• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 564
  • Last Modified:

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
0
mathew_s
Asked:
mathew_s
  • 3
  • 2
2 Solutions
 
slightwv (䄆 Netminder) 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
 
mathew_sAuthor 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) 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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now