Solved

Oracle Date Format Issue

Posted on 2013-10-29
5
556 Views
Last Modified: 2013-11-03
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
Comment
Question by:mathew_s
  • 3
  • 2
5 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 39609074
The string must match the format mask:

TO_DATE('July 29, 2013','Month DD, YYYY')
0
 
LVL 1

Author Comment

by:mathew_s
ID: 39609202
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
 
LVL 1

Accepted Solution

by:
mathew_s earned 0 total points
ID: 39609229
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39609432
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
 
LVL 1

Author Closing Comment

by:mathew_s
ID: 39619691
I believe both NLS_DATE_FORMAT and format mask were issues so points split.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Queries 15 38
Oracle SQL Select unique values from two columns 4 52
MULTIPLE DATE QUERY 15 76
Problem with duplicate records in Oracle query 16 25
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question