Luis

> I'm trying to find a record in a table with some alarms.
> In this case the user chooses the day of month to be warned,
> with the possibility to be warned several days before the final day
> And until that day he will be warned
>
snip
> Now without sucess  :
> datetimepicker1 date = 2007-11-30
> dia = 1 ( The day to be warned )
> diasmais =4  ( days before to be warned )
>
> Doenst work, because (dia-diasmais) =  -3
>
> So , how can i solve this ?
> I can change diasmais to datetime... or ... ?
>

As Robert says date arithmetic is a bit of a problem.  As I understand it it
is only where the test date+diasmais crosses a month boundary that the
problem arises.  In this case a query like the following may well work for
you.

Select * from Table1
where
'2007-11-30' BETWEEN mes1 AND mes2 AND
((extract(day from '2007-11-30') > extract(day from cast('2007-11-30' as
date)+diasmais)) AND
(dia > extract(day from '2007-11-30') or dia <= extract(day from
cast('2007-11-30' as date)+diasmais)))
OR
((extract(day from '2007-11-30') <= extract(day from cast('2007-11-30' as
date)+diasmais)) AND
(dia between extract(day from '2007-11-30') and extract(day from
cast('2007-11-30' as date)+diasmais)))


John

Luis


I think this is the first time you've mentioned that you want the alarm to repeat each month. It gets a bit more complex but its still possible. You need to extract the year and month from the current date and use that to build the comparison strings. Something like

CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS CHAR(4)) + '-' + CAST(EXTRACT(MONTH FROM CURRENT_DATE) AS CHAR(2) + '-' + startdate) AS DATE

should give you the date for the start of the range, do the same for the end of the range and if CURRENT_DATE is between them pop up your alarm.

If you're using an up-to-date verion of DBISAM you could always create a custom function and do the calculation in there, probably easier ro understand and code.

Roy Lambert

Luis,

<< Thanks Tim, but the results are the same.
The "key" its in this line :
EXTRACT(DAY FROM <DateConstant>) BETWEEN (dia-diasmais) and Dia
but i really dont know how to solve it.
The question seems simple, If I need only to find the day, its simple, but
day Minus some days ... or <DateConstant> Plus some days ???
I can change all the fields if needed, to dates, probably, but then... >>

So, the issue is crossing month boundaries due to the addition or
subtraction ?  If so, then Roy's answer will be what you want - you'll need
to build the dates as literals and cast them to actual dates in order to
have the date math work correctly across month boundaries.

--
Tim Young
Elevate Software
www.elevatesoft.com

On Wed, 14 Nov 2007 16:36:32 -0000, John Hay wrote:

> As Robert says date arithmetic is a bit of a problem.  As I understand it it
> is only where the test date+diasmais crosses a month boundary that the
> problem arises.  In this case a query like the following may well work for
> you.
>
> Select * from Table1
> where
> '2007-11-30' BETWEEN mes1 AND mes2 AND
> ((extract(day from '2007-11-30') > extract(day from cast('2007-11-30' as
> date)+diasmais)) AND
> (dia > extract(day from '2007-11-30') or dia <= extract(day from
> cast('2007-11-30' as date)+diasmais)))
> OR
> ((extract(day from '2007-11-30') <= extract(day from cast('2007-11-30' as
> date)+diasmais)) AND
> (dia between extract(day from '2007-11-30') and extract(day from
> cast('2007-11-30' as date)+diasmais)))
>
>
> John

Just PERFECT.

Using cast... etc... yes that's it.
I never thought that a simple  "new function" in the app would give me so
much headache

Thank you, ALL.