I was wondering if somebody help me with the following problem:

I am creating queries against an Oracle table with ( among other fields
) has
two datetime fields for tracking events. The two datetime fields are:

START_DT
END_DT

I need to be able to query records based on :

1) Given a start date and start time, end date and end time I need to
find all the events  under the start and end date constarints.

For example in the database I have records where :

START_DT =

08:00:00 AM, 08/16/1999

END_DT =

09:00:00 AM, 08/19/1999

Now I need to know how can I get the records where the end date and time
is

<= 08:30:00 AM, 08/18/1999

and the startdate time is :

I would appreciate any help I can get.

Regards,

Nick

where start_dt >= to_date('10/16/1990 08:30:00 AM',  'mm/dd/yyyy
HH:MI:SS AM') and end_dt <= to_date(as above)
John

where start_dt >= to_date('10/16/1990 08:30:00 AM',  'mm/dd/yyyy
HH:MI:SS AM') and end_dt <= to_date(as above)
John

Thanks John,

Would it be too much if I also ask how can you find what times I do not
have any records in my table?

For example :

If I have a booking for a day that starts at 8:30 on Jan 01, 2000 until
10:30 Jan 01, 2000, how can I create a query saying that after 10:30 on
Jan 01, 2000 I have no records in my table?

Thanks again,

Nick

Do you mean:
select count(*) from table where start_dt > to_date('01/20/1999 08:30
AM', 'mm/dd/yyyy  HH:MI AM')
Another way, in Delphi is to do a regular query, open, and
if EOF and BOF then
EOF and BOF will both be true if the query returned no rows.

John

Thanks John,

No I do not mean that though. What I mean is this:

Suppose that my table has START_DT and END_DT datetime fields. Then
suppose that the following records are in the table :

START_DT                                END_DT
01/20/1999 08:30                    01/20/1999 10:30
01/20/1999 12:30                    01/20/1999 14:30

What I want is to be able to query the table for  01/20/1999

from Start Time = 07:30

to End Time = 18:30

and to get back

START_DT                                END_DT
01/20/1999 07:30                    01/20/1999 08:30
01/20/1999 10:30                    01/20/1999 12:30
01/20/1999 14:30                    01/20/1999 18:30

As you can see, these will be the times for which there is no record in
my table.

Thanks again,

Nick

Essentially, SQL can give you back a row corresponding to a criterion,
or it can inform you that no rows satisfy the criterion, but it cannot
return non-rows.  What you would have to do is add rows for the times
you want to check, and have a flag column that signified
available/unavailable.  Alternatively, you could write a stored
procedure that would query the database and then return the available
times, as computed according to some rules that you implemented within
the stored procedure.

John