At 12:05 PM 17/11/2005 +0000, you wrote:

>SELECT WORK_DATE FROM OBJECTS_IN_WORK
>WHERE WORK_DATE between '12-may-2005' AND '14-apr-2005'
>
>In addition hB put me onto this.
>In a dialect 3 database this will NOT return rows where work_date is
>on 14-apr-2005.
>The solution is to use
>
>SELECT WORK_DATE FROM OBJECTS_IN_WORK
>WHERE cast(WORK_DATE as date) between '12-may-2005' AND '14-apr-2005'
>
>A dialect 1 db has a similar flaw.
>cf The Firebird Book pg 156.

Except it isn't a *flaw*, Mick. The time part of a timestamp search value
(dialect 1 DATE type) is "00:00:00.0000" unless it's provided for the
search. This query is not actually asking for anything with times after
midnight on 14-apr-2005. The flaw lies in the developer's misconception of
the difference between DATE and TIMESTAMP values.

The developer writes:

SELECT WORK_DATE FROM OBJECTS_IN_WORK
WHERE ((WORK_DATE >= '20050912') AND (WORK_DATE <
'20050914'))

But WORK_DATE is a timestamp, so the engine coerces the search values to
timestamp and the query is actually this:

SELECT WORK_DATE FROM OBJECTS_IN_WORK
WHERE ((WORK_DATE >= '20050912 00:00:00.0000')
AND (WORK_DATE <= '20050914 00:00:00.0000'))

Casting everything to DATE is fine for Dialect 3; but the alert developer
will add a day to the end of the second argument of BETWEEN to extend the
search to the end of the day:

For example,

SELECT WORK_DATE FROM OBJECTS_IN_WORK
WHERE ((WORK_DATE >= '20050912')
AND (WORK_DATE < '20050914' +1))

./hb