Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 10 of 14 total |
help with this sql , days, alerts ... |
Sat, Nov 10 2007 2:39 PM | Permanent Link |
"Luis M. Norberto" | 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 |
Sat, Nov 10 2007 3:49 PM | Permanent Link |
"Robert" | 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 |
Sat, Nov 10 2007 4:04 PM | Permanent Link |
"Luis M. Norberto" | 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 |
Sat, Nov 10 2007 4:37 PM | Permanent Link |
"Robert" | "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 |
Mon, Nov 12 2007 5:43 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Nov 12 2007 5:54 PM | Permanent Link |
"Luis M. Norberto" | 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 |
Tue, Nov 13 2007 9:15 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Nov 14 2007 6:50 AM | Permanent Link |
"Luis M. Norberto" | 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 |
Wed, Nov 14 2007 7:49 AM | Permanent Link |
"Robert" | "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 |
Wed, Nov 14 2007 9:51 AM | Permanent Link |
"Luis M. Norberto" | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, June 12, 2024 at 01:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |