Solved

Oracle Date Format Issue

Posted on 2013-10-29
5
559 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 77

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 77

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

696 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