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

So :
Using datetimepicker1 to do the tests.

Table1:
id, dia, diasmais = integer values
mes1, mes2 = datetime values

example with sucess :
datetimepicker1 date = 2007-11-20
dia = 23  ( The day to be warned )
diasmais =4   ( days before to be warned )
mes1 = 2000-01-01   
mes2 = 2010-12-31

(dia-diasmais) = 19 , so 20 is between 19 and 3

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 ... ?

Heres the sql im using:

var
result:integer;
  begin
  With Query1 do begin
  Close;
  Sql.Clear;
  Sql.Add('Select id from Table1');
  Sql.Add('where '+Formatdatetime('dd',(datetimepicker1.Date))+' between
(dia-diasmais) and (Dia)');
  Sql.Add('and '+QuotedStr(AnsidateTostr(datetimepicker11.Date))+('
between mes1 and mes2'));
   execsql;
 end;

Result:=Query1.RecordID;

 if Result>0 then table1.Locate('ID',Result,[]);

Tnx for your time

Compare dates instead of day. Date between date1 and date1 + 4. The date
math will take care of the problem for you.

Robert

"Luis M. Norberto" <"r_roll(Obvious)"@hotmail.com> wrote in message
news:17ilt6w2v28ry$.qbl85ra423he.dlg@40tude.net...
> 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
>
> So :
> Using datetimepicker1 to do the tests.
>
> Table1:
> id, dia, diasmais = integer values
> mes1, mes2 = datetime values
>
> example with sucess :
> datetimepicker1 date = 2007-11-20
> dia = 23  ( The day to be warned )
> diasmais =4   ( days before to be warned )
> mes1 = 2000-01-01
> mes2 = 2010-12-31
>
> (dia-diasmais) = 19 , so 20 is between 19 and 3
>
> 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 ... ?
>
> Heres the sql im using:
>
> var
> result:integer;
>   begin
>   With Query1 do begin
>   Close;
>   Sql.Clear;
>   Sql.Add('Select id from Table1');
>   Sql.Add('where '+Formatdatetime('dd',(datetimepicker1.Date))+' between
> (dia-diasmais) and (Dia)');
>   Sql.Add('and '+QuotedStr(AnsidateTostr(datetimepicker11.Date))+('
> between mes1 and mes2'));
>    execsql;
>  end;
>
> Result:=Query1.RecordID;
>
>  if Result>0 then table1.Locate('ID',Result,[]);
>
> Tnx for your time

Ok, so if i understand the best way is changing the field Dia ( in this
case day 1 ) to a Tdadtetime like 2007-08-01 ?



On Sat, 10 Nov 2007 15:44:36 -0500, Robert wrote:

> Compare dates instead of day. Date between date1 and date1 + 4. The date
> math will take care of the problem for you.
>
> Robert
>
> "Luis M. Norberto" <"r_roll(Obvious)"@hotmail.com> wrote in message
> news:17ilt6w2v28ry$.qbl85ra423he.dlg@40tude.net...
>> 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
>>
>> So :
>> Using datetimepicker1 to do the tests.
>>
>> Table1:
>> id, dia, diasmais = integer values
>> mes1, mes2 = datetime values
>>
>> example with sucess :
>> datetimepicker1 date = 2007-11-20
>> dia = 23  ( The day to be warned )
>> diasmais =4   ( days before to be warned )
>> mes1 = 2000-01-01
>> mes2 = 2010-12-31
>>
>> (dia-diasmais) = 19 , so 20 is between 19 and 3
>>
>> 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 ... ?
>>
>> Heres the sql im using:
>>
>> var
>> result:integer;
>>   begin
>>   With Query1 do begin
>>   Close;
>>   Sql.Clear;
>>   Sql.Add('Select id from Table1');
>>   Sql.Add('where '+Formatdatetime('dd',(datetimepicker1.Date))+' between
>> (dia-diasmais) and (Dia)');
>>   Sql.Add('and '+QuotedStr(AnsidateTostr(datetimepicker11.Date))+('
>> between mes1 and mes2'));
>>    execsql;
>>  end;
>>
>> Result:=Query1.RecordID;
>>
>>  if Result>0 then table1.Locate('ID',Result,[]);
>>
>> Tnx for your time

"Luis M. Norberto" <"r_roll(Obvious)"@hotmail.com> wrote in message
news:1920wcsqd0gtf.6a5brpcvzp1y$.dlg@40tude.net...
> Ok, so if i understand the best way is changing the field Dia ( in this
> case day 1 ) to a Tdadtetime like 2007-08-01 ?
>

Not sure, I have not looked at your SQL in detail but in general, using the
day for those type comparisons is always a mess, for the reasons you
discovered. What happens after month end, after year end, etc. Date is a
float, and very easy and foolproof to use in comparisons. The date routines
in Delphi and SQL take care of all the mess for you. Mydate between
("2007-11-09" and ("2007-11-09" + 7) or something like that always works.

Robert

>
>
> On Sat, 10 Nov 2007 15:44:36 -0500, Robert wrote:
>
>> Compare dates instead of day. Date between date1 and date1 + 4. The date
>> math will take care of the problem for you.
>>
>> Robert
>>
>> "Luis M. Norberto" <"r_roll(Obvious)"@hotmail.com> wrote in message
>> news:17ilt6w2v28ry$.qbl85ra423he.dlg@40tude.net...
>>> 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
>>>
>>> So :
>>> Using datetimepicker1 to do the tests.
>>>
>>> Table1:
>>> id, dia, diasmais = integer values
>>> mes1, mes2 = datetime values
>>>
>>> example with sucess :
>>> datetimepicker1 date = 2007-11-20
>>> dia = 23  ( The day to be warned )
>>> diasmais =4   ( days before to be warned )
>>> mes1 = 2000-01-01
>>> mes2 = 2010-12-31
>>>
>>> (dia-diasmais) = 19 , so 20 is between 19 and 3
>>>
>>> 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 ... ?
>>>
>>> Heres the sql im using:
>>>
>>> var
>>> result:integer;
>>>   begin
>>>   With Query1 do begin
>>>   Close;
>>>   Sql.Clear;
>>>   Sql.Add('Select id from Table1');
>>>   Sql.Add('where '+Formatdatetime('dd',(datetimepicker1.Date))+' between
>>> (dia-diasmais) and (Dia)');
>>>   Sql.Add('and '+QuotedStr(AnsidateTostr(datetimepicker11.Date))+('
>>> between mes1 and mes2'));
>>>    execsql;
>>>  end;
>>>
>>> Result:=Query1.RecordID;
>>>
>>>  if Result>0 then table1.Locate('ID',Result,[]);
>>>
>>> Tnx for your time

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 >>

What are the dia-diasmais and Dia values set to before running the query ?
Are they actually integer values like you show ?

--
Tim Young
Elevate Software
www.elevatesoft.com

On Mon, 12 Nov 2007 17:43:14 -0500, Tim Young [Elevate Software] wrote:

> 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 >>
>
> What are the dia-diasmais and Dia values set to before running the query ?
> Are they actually integer values like you show ?

Dia = integer
Diasmais = integer
But I can change diasmais to Tdatetime, if it helps.

The table with example:

Id        -  DiasMais   -  Dia     - Alert  - mes1      -  mes2
(AutoInc  -  Integer    -  Integer -  Memo  - TdateTime -Tdatetime)
321      -    3        -    1     -  xxxx  - 2000-01-01 - 2010-12-31
329      -    4        -    15     - xxxxx - 2000-01-01  - 2010-12-31

Tnx

Luis,

<< The table with example: >>

Okay, use this:

Select id from Table1
where <DateConstant> BETWEEN mes1 AND mes2 AND
EXTRACT(DAY FROM <DateConstant>) BETWEEN (dia-diasmais) and Dia

--
Tim Young
Elevate Software
www.elevatesoft.com

On Tue, 13 Nov 2007 21:15:29 -0500, Tim Young [Elevate Software] wrote:

> Luis,
>
> << The table with example: >>
>
> Okay, use this:
>
> Select id from Table1
> where <DateConstant> BETWEEN mes1 AND mes2 AND
> EXTRACT(DAY FROM <DateConstant>) BETWEEN (dia-diasmais) and Dia

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...

Thank you

"Luis M. Norberto" <"r_roll(obvious)hotmail.com"> wrote in message
newsthipgkx85t.6ukdm4bqdwvz.dlg@40tude.net...
> I can change all the fields if needed, to dates, probably, but then...
>

You don't need to "change all the fields". You just need to do the math
using dates, not month and day. Both Delphi and SQL have all the logic to
handle that. 2007-11-14 - 15 = 2007-10-31. WHERE MyDate between BegDate and
BegDate + 22; Etc. Date is a float internally, and you don't have to worry
about the internals of month and day. If you need to build a date, just use
CAST('2007-11-' + DayAsString as Date). I think you're stuck because you
think you have to compare days. Just compare full dates.

Robert

On Wed, 14 Nov 2007 07:45:09 -0500, Robert wrote:

> "Luis M. Norberto" <"r_roll(obvious)hotmail.com"> wrote in message
> newsthipgkx85t.6ukdm4bqdwvz.dlg@40tude.net...
>> I can change all the fields if needed, to dates, probably, but then...
>>
>
> You don't need to "change all the fields". You just need to do the math
> using dates, not month and day. Both Delphi and SQL have all the logic to
> handle that. 2007-11-14 - 15 = 2007-10-31. WHERE MyDate between BegDate and
> BegDate + 22; Etc. Date is a float internally, and you don't have to worry
> about the internals of month and day. If you need to build a date, just use
> CAST('2007-11-' + DayAsString as Date). I think you're stuck because you
> think you have to compare days. Just compare full dates.
>
> Robert

Robert
I understand what you say, but look at this  :
In your example "BegDate" its date, ok, i change "dia" to date.
Now, the present date is 2007-08-15, the user want a monthly alarm at day
1, and to be warned some days before, lets say : 5 days.
The user saves for the first time the record.:
Dia.value:= 2007-09-01,  diasmais.value:=5

If mydate(now) for example is 2007-11-27, then '2007-11-27' between
'2007-09-01' and '2007-09-01'-5 , for example, this never works.
Thats : mydate between dia and dia-diasmais
I don't see in this case how can i use full date comparisons.
If you can help me ... you're welcome...
Thank you