Subject Re: [ib-support] comparing the TIME part of a DATE field
Author Helen Borrie
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:
> 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
> t:TDateTime;
> ...
> t:=FieldByName('start_time').AsDateTime;
> ...
>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'"
>"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

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.