"John Hay" <j.haywithoutdot@crbsolutionsremoveallthis.co.uk> wrote in
message news:0D7B6239-F6C5-4463-85B8-727A0FF485F7@news.elevatesoft.com...
>
> I think you have to check the next year as well to catch birthdates in
> January when the process is run in December ie add
>

You don't. "current date + 30" is just a date, which is a number. At this
point, it knows nothing about years.

Robert

Robert,

I think John is correct. Suppose the birth date is "1990-01-01" and the
current date is "2008-12-20". The expression

CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS
CHAR(4))+SUBSTRING(CAST(datefield AS
CHAR(10)) FROM 5) AS DATE)

will evaluate to '2008-01-01' and that is not between CURRENT_DATE and
CURRENT_DATE + 30, so we must also check the next year as John said.

However, this is still going to fail if the birth date is Feb,29 if the
current year isn't a leap year so, considering Roy and John's code and
considering this additional case, the code should be:


SELECT ...
WHERE
COALESCE(CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS
CHAR(4))+SUBSTRING(CAST( datefield AS CHAR(10)) FROM 5) AS DATE),
CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS CHAR(4))+'-03-01' AS DATE))
BETWEEN CURRENT_DATE AND CURRENT_DATE + 30
OR
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

--
Fernando Dias
[Team Elevate]

I thank you all very much. This would be a great university question.
Best Regards
Dave



"Fernando Dias [Team Elevate]" <fernandodias.removthis@easygate.com.pt> wrote:

Robert,

I think John is correct. Suppose the birth date is "1990-01-01" and the
current date is "2008-12-20". The expression

CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS
CHAR(4))+SUBSTRING(CAST(datefield AS
CHAR(10)) FROM 5) AS DATE)

will evaluate to '2008-01-01' and that is not between CURRENT_DATE and
CURRENT_DATE + 30, so we must also check the next year as John said.

However, this is still going to fail if the birth date is Feb,29 if the
current year isn't a leap year so, considering Roy and John's code and
considering this additional case, the code should be:


SELECT ...
WHERE
COALESCE(CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS
CHAR(4))+SUBSTRING(CAST( datefield AS CHAR(10)) FROM 5) AS DATE),
CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS CHAR(4))+'-03-01' AS DATE))
BETWEEN CURRENT_DATE AND CURRENT_DATE + 30
OR
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

--
Fernando Dias
[Team Elevate]

> However, this is still going to fail if the birth date is Feb,29 if the
> current year isn't a leap year

Nice catch Fernando

John

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?




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

> However, this is still going to fail if the birth date is Feb,29 if the
> current year isn't a leap year

Nice catch Fernando

John

Dave


As a guess you have an invalid / null entry in a date field.

Roy Lambert [Team Elevate]

Hi Roy
I have the SQL in the Database System Utility and it works fine.
I copy and paste it into my IDE and activate it at design time and no problem.
If I run the program in the IDE I get the error.
However if I run the compiled program from windows explorer - no error?

Why would I get the error when I Run F9 the program from the IDE yet
it works from the compiled code fine?

Thanks again Roy
BTW. Using Vista and Delphi 7 - Delphi 7 is being run as administrator



Roy Lambert <roy.lambert@skynet.co.uk> wrote:

Dave


As a guess you have an invalid / null entry in a date field.

Roy Lambert [Team Elevate]

You are correct, it is because there is a NULL date.
Can I add to the SQL AND DateField is Not NULL
I would like to get it so it can work at design time as well?

Thanks again Roy


Dave B <david@itfx.com.au> wrote:

Hi Roy
I have the SQL in the Database System Utility and it works fine.
I copy and paste it into my IDE and activate it at design time and no problem.
If I run the program in the IDE I get the error.
However if I run the compiled program from windows explorer - no error?

Why would I get the error when I Run F9 the program from the IDE yet
it works from the compiled code fine?

Thanks again Roy
BTW. Using Vista and Delphi 7 - Delphi 7 is being run as administrator



Roy Lambert <roy.lambert@skynet.co.uk> wrote:

Dave


As a guess you have an invalid / null entry in a date field.

Roy Lambert [Team Elevate]

Dave


>You are correct, it is because there is a NULL date.
>Can I add to the SQL AND DateField is Not NULL
>I would like to get it so it can work at design time as well?

No reason why not.

Since NULL's stand for *unknown* you would have no idea wether its this individual's birthday or not. However, unless you want to assume it is and give them a pressie every day its best to assume its not.

Roy Lambert [Team Elevate]

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