John

>  I suspect that casting an illegal date resulting as
> null is particular to dbisam and will raise an exception in other dbms
> (elevatedb ?).

Yes, it is.
It's a shortcut to obtain a shorter statement, but this SQL is not going to
work in EDB:
cast('2001-02-29' as date) will result in a conversion error.

--
Fernando Dias
[Team Elevate]

Thanks John,

Still an error in design time. I just wrapped the WHERE in IF .. IS NULL
which seems to have it sorted, so I just avoid NULL fields.

Thank you very much for all you help.
Best Regards
Dave



"John Hay" <j.haywithoutdot@crbsolutionsremoveallthis.co.uk> wrote:

Dave

> I am trying to use the SQL, in the Database System Utility it works fine.
> In design time in Delphi it works fine.
> BUT if I open the query at runtime I get
>
> EConvertError "2008
>
> I cant work out why?

I guess this is when you run it inside the ide with stop on delphi
exceptions set to true.  The exception will be "swallowed" when the app runs
outside the debugger.  I suspect that casting an illegal date resulting as
null is particular to dbisam and will raise an exception in other dbms
(elevatedb ?).

An alternative (albeit very wordy !) approach to deal explicitly with 29/02
birthdays might be

WHERE

IF(EXTRACT(MONTH FROM datefield) = 2 AND EXTRACT(day FROM datefield) = 29
AND
(NOT (EXTRACT(YEAR FROM current_date) MOD 4 = 0 AND
(EXTRACT(YEAR FROM current_date) MOD 100 <> 0 or
EXTRACT(YEAR FROM current_date) MOD 400 = 0))) then  /* edge case of 29/02
in non leap YEARs */

CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS CHAR(4))+'-03-01' AS DATE)
BETWEEN CURRENT_DATE AND CURRENT_DATE + 30

ELSE

(CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS
CHAR(4))+SUBSTRING(CAST(datefield AS
CHAR(10)) FROM 5) AS DATE) BETWEEN CURRENT_DATE AND CURRENT_DATE + 30)) /*
all others */

OR

IF(EXTRACT(MONTH FROM datefield) = 2 AND EXTRACT(day FROM datefield) = 29
AND
(NOT (EXTRACT(YEAR FROM current_date)+1 MOD 4 = 0 AND
(EXTRACT(YEAR FROM current_date)+1 MOD 100 <> 0 OR
EXTRACT(YEAR FROM current_date)+1 MOD 400 = 0))) THEN  /* edge case of 29/02
in non leap YEARs */

CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE)+1 AS CHAR(4))+'-03-01' AS DATE)
BETWEEN CURRENT_DATE AND CURRENT_DATE + 30

ELSE

(CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE)+1 AS
CHAR(4))+SUBSTRING(CAST(datefield AS
CHAR(10)) FROM 5) AS DATE) BETWEEN CURRENT_DATE AND CURRENT_DATE + 30) ) /*
all others */

John

Dave

> Still an error in design time. I just wrapped the WHERE in IF .. IS NULL
> which seems to have it sorted, so I just avoid NULL fields.

Forgot about those birthdates you don't know about !  Glad its working

Cheers

John