At 09:52 AM 22-09-02 +0000, you wrote:

>I have shift start time and end time stored in 2 DATE fields of a
>table, like:
>
>CREATE TABLE Shifts
>(
> shift_id varchar(5) NOT NULL,
> start_time date NOT NULL,
> end_time date NOT NULL,
> PRIMARY KEY (shift_id)
>);
>
>
>When I read the containt of - say - the START_TIME field, with
>
>var
> t:TDateTime;
>begin
> ...
> t:=FieldByName('start_time').AsDateTime;
> ...
>end;
>
>I can see that no integer part is stored in the field.
>
>For instance, '20:00:00' is read as 0.83333...
>
>The problem is:
>
>I find no way to query via SQL all shifts whose start time is - say -
>at or later than 20:00:00 .
>
>If I write what I thought obvious
>
>"select * from shifts where start_time>='0000-00-00 20.00.00'"
>
>I get the error message "value exceeds the range for valid dates".
>
>I can see that the field containt is displayed as
>
>'30-12-99 20.00.00' in a TDBGrid.
>
>However, writing
>
>"select * from shifts where start_time>='1999-12-30 20.00.00'"
>
>or
>
>"select * from shifts where start_time>='2099-12-30 20.00.00'"
>
>- while no error message is raised - gets an empty set, which is not
>what I want, of course.

First of all --- notice that a full-stop (period, punkt..) isn't the
correct separator for time literals!!! It can only be a colon, except for
the thousandths part at the end.

However, you can't perform time manipulations on a DATE type - it needs to
be a TIMESTAMP (unless of course you are using IB 5.x or lower).

Also, if using Fb or IB 6.x, you need to explicitly cast date/time literals
in some conditions, e.g.

select * from shifts where start_time >= CAST('1999-12-30 20:00:00:0000' AS
TIMESTAMP)

This will only get you the records later than 30 December 1999. If you
want to store date-independent times of day, you need to use a TIME type,
which stores a number equivalent to 0000-00-00 HH:MM:SS.nnn and ignores the
date part when reading it.

heLen