*** Need BETWEEN Dates to be inclusive in ORACLE PL/SQL

Hi,
I have a table with a date/time column that I need to query from.

When I use:

WHERE CALL_DATE BETWEEN TO_DATE('10/11/2010', 'MMDDYY) AND TO_DATE('10/12/2010', 'MMDDYY)

The results only bring back 10/11/2010.  I need the two date parameters to be inclusive.

Please help.  Thanks!

dunkin1969Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ajexpertCommented:
Date format is not correct
TO_DATE('10/11/2010', 'MM/DD/YYYY) AND TO_DATE('10/12/2010', 'MM/DD/YYYY)

Open in new window

0
johnsoneSenior Oracle DBACommented:
I believe you want something like this:
WHERE CALL_DATE BETWEEN TO_DATE('10/11/2010', 'MM/DD/YYYY) AND TO_DATE('10/12/2010', 'MM/DD/YYYY) + (86399/86400)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ajexpertCommented:

SELECT * FROM <YOURTABLE>
WHERE CALL_DATE BETWEEN TO_DATE('10/11/2010', 'MM/DD/YYYY') AND TO_DATE('10/12/2010', 'MM/DD/YYYY')

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

dunkin1969Author Commented:
ajexpert,
your code doesn't return the desired results.  Its excludes the 10/12/2010 records.

johnsone,
your code works!!!  can you explain to me the significance of      + (86399/86400)   ?

Thanks !!!
0
johnsoneSenior Oracle DBACommented:
86400 is the number of seconds in 1 day.  86399 is one less than that.  Since you are not specifying a time in your date it defaults to midnight.  By adding 86399/86400 to a date at midnight, that takes us to 23:59:59 on that day and would therefore include all times in that date.  Your original query was only including 1 second of 10/13/2010, the 1 second that occurred at midnight.
0
sdstuberCommented:
dates are down to the second.


TO_DATE('10/12/2010', 'MM/DD/YYYY')  is 2010-10-12 00:00:00

so,  you're excluding all seconds from 00:00:01  to 23:59:59.

date math is in units of days.  86399/86400 is 23 hours, 59 minutes 59 seconds
0
dunkin1969Author Commented:
Thanks for the explanation johnsone and sdstuber.  Makes perfect sense and I actually understand it!
0
dunkin1969Author Commented:
Yikes,
I forgot to ask how to adjust the code to qualify for a SINGLE date (10/11/2010)....

This uses the BETWEEN suntax:

WHERE CALL_DATE BETWEEN TO_DATE('10/11/2010', 'mm/dd/yyyy') AND TO_DATE('10/11/2010', 'mm/dd/yyyy') + (86399/86400)

But how can I utilize this format to include the hours/seconds? :

WHERE CALL_DATE = TO_DATE('10/11/2010', 'mm/dd/yyyy')

Thanks!!
Maybe I'll post to a new question if I can't get responses here.
0
johnsoneSenior Oracle DBACommented:
For a single date use the same between syntax:

WHERE CALL_DATE BETWEEN TO_DATE('10/11/2010', 'mm/dd/yyyy') AND TO_DATE('10/11/2010', 'mm/dd/yyyy') + (86399/86400)

If you do not want to use the between syntax, then you are going to have to truncate your date column to remove the time:

WHERE TRUNC(CALL_DATE) = TO_DATE('10/11/2010', 'mm/dd/yyyy')

However, be aware that this will not allow the use of an index on CALL_DATE (if one exists).  To get around this you would have to create a function based index on the truncated column.  Not difficult, but it is another index to maintain.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.