At 07:13 PM 13-08-02 +0000, you wrote:

>This 'end of April minus a month' query returns no records:
>
>SELECT F_PROCESSDATE
>FROM T_LIMZ1_SAMPLES
>WHERE F_PROCESSDATE Between cast('30.04.2002' as date) And (cast
>('30.04.2002' as date) - 31)
>
>And yet this 'start of April plus a month' returns 2000+ records in
>the correct date range to 1-May-2002:
>
>SELECT F_PROCESSDATE
>FROM T_LIMZ1_SAMPLES
>WHERE F_PROCESSDATE Between cast('31.03.2002' as date) And (cast
>('31.03.2002' as date) + 31)
>
>Why can FB successfully add 31 days but not subtract them?

It >>can<< subtract days.

...and does...

...and doesn't find any days that meet the criterion of
BETWEEN CAST('30.04.2002' AS DATE) AND CAST('30.03.2002' AS DATE)

because it doesn't speak English, only SQL...

It's not "between" as in "between thee and me" but
between as a shorthand for
value1 >= value2 AND value1 <= value3

cheers,
heLen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
______________________________________________________________________