Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4023
  • Last Modified:

*** 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!

0
dunkin1969
Asked:
dunkin1969
  • 3
  • 3
  • 2
  • +1
3 Solutions
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now