Try reversing the two arguments to the BETWEEN clause such that the one with
the lesser date value is first as in your second example that works.

WHERE F_PROCESSDATE Between (cast('30.04.2002' as date)-31) And cast
('30.04.2002' as date)

Bill Meaney

>
> [sorry for double post. Forgot a meaningful subject line. Would be
> nice if Yahoo had add an option to flag posts lacking such.]
>
> 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?