Solved

Oracle

Posted on 2011-02-24
6
748 Views
Last Modified: 2012-05-11
I can run rings around anyone in SQL, but it's been a while since I've used Oracle with SQL Plus

I've tried everything I could think of or find on line, so you're going to have to come up with something really good to solve this.

I know some of the paid_dates are null

I want the paid_date of any invoice that was paid within 3 days of the approved_date.  Should be simple.  It's not!  I keep getting the error below.  Tried case, nvl, is not null, everything.  I saw an article about it being a known error by Oracle.  Need a solution.  I'm new on the job and want to prove my worth!

select to_date(to_char(paid_date),'RRRR-MM-DD') "PAID DATE"
from Ellipse.MSF260 a
where nvl(paid_date,1900-01-01) between '2010-07-01' and '2011-02-28'
and to_date(to_char(paid_date),'RRRR-MM-DD') between to_date(to_char(approved_date),'RRRR-MM-DD') and to_date(to_char(approved_date),'RRRR-MM-DD') +3

Error:'
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
0
Comment
Question by:glendawi
6 Comments
 
LVL 7

Accepted Solution

by:
MrNed earned 250 total points
Comment Utility
Are you sure the default date format is YYYY-MM-DD?, try wrapping the two hardcoded dates with a to_date().
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
First: why go from date to char and back to date?
to_date(to_char(paid_date),'RRRR-MM-DD')

If you don't want the time, just trunc(paid_date)

Now to the error:
nvl(paid_date,1900-01-01) between '2010-07-01' and '2011-02-28'


Never use implicit conversions.
Use to_date('2010-07-01','YYYY-MM-DD')
For any string to date comparisons.

You are also missing quotes on the date in the NVL.
0
 
LVL 7

Expert Comment

by:MrNed
Comment Utility
And is this a typo? you need quotes:

where nvl(paid_date,1900-01-01)
0
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.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility



select paid_date "PAID DATE"
from Ellipse.MSF260 a
where paid_date between date '2010-07-01' and date '2011-02-28'
and approved_date > paid_date
and  approved_date - paid_date < 3
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
your error is because of your double conversion, one of which is implicit.

Not only is it erroneous, it's also inefficient
0
 

Author Closing Comment

by:glendawi
Comment Utility
Thanks SO MUCH.
But I also had to add
where paid_date is not null
and paid_date <> ' '
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.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now