At 11:26 AM 12/11/2004 -0500, you wrote:

>Using FB 1.5, I have a dialect 1 database with a table defined as
>such:
>
>create table T1 (
> del_date date,
> time_window_start varchar(20),
> time_window_end varchar(20),
> arrival_time date);
>
>insert into T1 ('10/15/2004', '08:00 AM', '10:00 AM', '10/15/2004
>09:00:00');
>insert into T1 ('10/16/2004', '08:00 PM', '10:00 PM', '10/15/2004
>22:30:00');
>
>I need a way to tell whether the arrival time is between the
>time_window_start and time_window_end.
>
>To do this, I was trying this comparison:
>
>arrival_time between cast(del_date || ' ' || time_window_start as
>date) and cast(del_date || ' ' || time_window_end as date)
>
>However, FB is unable to recognize '08:00 AM' as a valid time format.
>
>Are there any options for effecting this comparison?

You have multiple problems here. First, a dialect 1 DATE type is a
timestamp, so it already has a TIME part. Whether this represents midnight
or some other time depends of course on how you stored that
timestamp. Since dialect 1 has no date-only type, you need somehow to
"zero-out" the time part before you can consider adding the time part (or
concatenating a different time part to the literal).

Your next problem is that the database engine uses a 24-hour clock, so the
strings you are storing as start and end times are not valid for
concatenation as a time literal. Hence, the string you store for '08:00 AM'
should be '08:00:00.0000" for it to be recognised as a date literal in
expressions. To be blunt, the time strings you are storing currently are a
few clicks better than useless.

The third problem is that, by storing just the times (even if they were
valid literals), there will be arithmetical complications with the start
and end times if the outcome of the (already complicated) parsing and date
arithmetic should resolve so that the start and end times are (or ought to
be) on different days.

In short, although there are complicated things you could do with triggers,
hidden fields and stored procedures, or by writing a UDF, the data
currently stored are unsafe for this search.

If you are determined to proceed with this concept anyway, you can convert
the currently stored strings into valid timestamp literals using a stored
procedure that parses the numerals into the format '00/00/00 hh:nn:00.0000'
and adds 12 to the Hours part if the string contains 'PM'.

This probably breaks your application code, so one way to make this work
would be to add two date columns to the tables, and do the parsing and
storing by calling your stored procedure in your Before Insert and Before
Update triggers. You'd also need another stored procedure that loops
through the existing data and calls this conversion procedure to populate
the new columns.

Then you still have the problem at search time of replacing the time part
of your stored arrival_date with midnight, so that you can arrive at the
equivalent of a date-only value, to which you can add your zero-date
timestamps to calculate the timestamps for your BETWEEN search. The
EXTRACT() function can help here, but you are not looking at a simple
expression....

./heLen