asked on

How to compare a time in Oracle?

Hi,

I need to retrieve record from the Oracle database by comparing with a time range? The column "OTime" is of Date datatype. I'm using ASP.

This is my SQL:
"SELECT * FROM Purchase WHERE (OTime BETWEEN to_date('" & from_time & "', 'HH24:MI:SS') AND to_date('"& to_time &"', 'HH24:MI:SS'))

There are no errors, but no records were retrieved. I have only stored the time into "OTime". Do I need to store the date as well?

Thanks!

BearT

Bigfam5

Try something like this

Select * from Purchase
where to_date(to_char(OTIME,'hh24miss'),'hh24miss')
between to_date('070000','hh24miss') and to_date('080000','hh24miss')

schwertner

DATE type in Oracle allways stores date and time. It seems that the SYSDATE is stored as date component.

Try

"SELECT * FROM Purchase WHERE (to_date(OTime,'HH24:MI:SS') BETWEEN to_date('" & from_time & "', 'HH24:MI:SS') AND to_date('"&
to_time &"', 'HH24:MI:SS'))

ASKER CERTIFIED SOLUTION

venkotch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

konektor

a variable of date datatype allways holds day/month/year information
so if you
DECLARE
vTime DATE;
BEGIN
SELECT to_date('13:27:11','HH24:MI:SS') INTO vTime FROM dual;
END;
vTime is this day, time 13:27:11, today 21-mar-2002 13:27:11

so if you want to compare only time you should convert it into number or char and compare chars