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

Oracle

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
glendawi
Asked:
glendawi
2 Solutions
 
MrNedCommented:
Are you sure the default date format is YYYY-MM-DD?, try wrapping the two hardcoded dates with a to_date().
0
 
slightwv (䄆 Netminder) Commented:
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
 
MrNedCommented:
And is this a typo? you need quotes:

where nvl(paid_date,1900-01-01)
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
sdstuberCommented:



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
 
sdstuberCommented:
your error is because of your double conversion, one of which is implicit.

Not only is it erroneous, it's also inefficient
0
 
glendawiAuthor Commented:
Thanks SO MUCH.
But I also had to add
where paid_date is not null
and paid_date <> ' '
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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