Query regarding date & time

I wanted to know how to get all the records of a particular day between two times. For ex : I want all the records of 15th May 2006 between 10 am and 5 pm. The datatype used is date. Plz let me know the query if anyone knows....

have a look at this thread - it might do for starters

i know the thread is about oracle rdb, but there might be enough that is applicable to standard oracle

You can compare dates with the usual operators, something like
... where your_date_col > to_date(your_lower_limit, it's format) ...

Hi,

Let us consider the table structure as


ID NAME Dat
---- ------------------------------ -----------------
1 Puvi 12-01-06 10:45:33
2 ss 2-01-06 12:45:56
3 dd 3-01-06 12:45:56

U can get the names of the people who have registered between 11 and 23 Hrs by using the below query

select name , to_char(dat,'dd-mm-yy') dat from test1 where to_char(dat,'hh24:mi:ss') between '11:00:00' and '23:00:00';

which will yeild
Name dat
---------------------------------
ss 12-01-06
dd 13-01-06

or if u need the name on a particular date then write this query

select name , to_char(dat,'dd-mm-yy')
from test1
where to_char(dat,'hh24:mi:ss') between '11:00:00' and '23:00:00'
and
to_char(dat,'dd-mm-yy')='12-01-06';

which yeilds,

NAME TO_CHAR(
------------------------------ --------
ss 12-01-06

Be careful with the BETWEEN keyword (and <, <=, >, >= for that matter). BETWEEN is inclusive of both dates, but if you do

BETWEEN '01-JAN-2006' AND '31-JAN-2006'

you will get data between 00:00:00 on 01/01 and 23:59:59 01/30. Basically you won't get 01/31 data because you are using a truncated date. If you truly want inclusive you need to create the statement that way.