Date Query Without Explicit Times

[njtornado]

We have an Oracle db that has dates stored with the hours, minutes & seconds. I've created a date query that will allow users to run a query on a date range by entering a begin date and an end date. The query runs but the resulting data is not 100% accurate unless I explicitly enter the ending hours/minutes/seconds.

example:
If the user enters a
begin date of: 03/01/2001 and
end date of: 03/03/2001
query result: 03/01/2001 00:00:00 AM through 03/03/2001 00:00:00 AM and OMITS the balance of the day of 03/03/2001.

The query works properly if I enter a
begin date of: 03/01/2001 and
end date of: 03/03/2001 11:59:59 PM
But I don't want the users to have to type in hh:mm:ss AM/PM in order to produce accurate results.

How do I modify the query so the user enters only the month, date and year but the query runs on the "whole/full" day and therefore ends correctly at 03/03/2001 11:59:59 PM by the user only typing in 03/03/2001?

 

[carp]

Try this:

SELECT col1, ..., coln
FROM my_table
WHERE TRUNC(date_column) BETWEEN first_date AND last_date;

This will have the effect of looking at each date as though it occurred at midnight.