search between two dates

[Analyste2023 0]

hello Delphi geniuses, I hope you are well, I have a problem, how to filter the data between two dates, i.e. the user wants to display only the data from 06/01/2023 to 06/30/2023 for example, thank you for your help

[PeterBelow 250]

  On 6/10/2023 at 1:03 PM, Analyste2023 said:

hello Delphi geniuses, I hope you are well, I have a problem, how to filter the data between two dates, i.e. the user wants to display only the data from 06/01/2023 to 06/30/2023 for example, thank you for your help

 

No data to provide any help on, sorry. We need more detail. If your UI view is populated from a database query just use an appropriate condition in the WHERE clause of the query. Most database engines support the BETWEEN operator for dates; just make sure to formulate the bounds using parameters and not date literals (which are sensitive to date formats, locales for client and server, great potential to cause a mess).

[programmerdelphi2k 238]

using "Between" dates:    

  Quote

select * from Sales

where order_date BETWEEN  cast('xx/xx/xxxx' as date)  AND cast('xx/xx/xxxx' as date)

order by order_date

Using "PARAMetrizations" in your FDQuery for example:

 

 

procedure TForm1.Button1Click(Sender: TObject);
begin
  SalesTable.Close;
  SalesTable.ParamByName('DateBegin').AsDate := StrToDate('06/01/2023');
  SalesTable.ParamByName('DateEnd').AsDate   := StrToDate('06/30/2023');
  SalesTable.Open;
end;

 

Edited June 10, 2023 by programmerdelphi2k

[Serge_G 90]

👍 programmerd2k response, and, with firedac, you can simplify his code to :

 

procedure TForm1.Button1Click(Sender : TObject)
begin
Salestable.open('',[DateFrom.Date,Dateto.Date]);
end;

 

Edited June 12, 2023 by Serge_G

[programmerdelphi2k 238]

  On 6/12/2023 at 6:15 AM, Serge_G said:

, you can simplify his code to :

Serge, there are so many possibilities that we often forget the obvious... good times when there weren't so many options  😂

[Analyste2023 0]

bonjour a tous ,merci a vous de m'avoir aider  vos intervention m'en beaucoup aider dans l'avancement de mon projet je suis fier d'etre parmi vous et tres content d'utiliser delphi pour les applications windows

[Analyste2023 0]

  On 6/10/2023 at 2:11 PM, PeterBelow said:

Aucune donnée pour fournir de l'aide, désolé. Nous avons besoin de plus de détails. Si votre vue d'interface utilisateur est remplie à partir d'une requête de base de données, utilisez simplement une condition appropriée dans la clause WHERE de la requête. La plupart des moteurs de base de données prennent en charge l'opérateur BETWEEN pour les dates ; assurez-vous simplement de formuler les limites en utilisant des paramètres et non des littéraux de date (qui sont sensibles aux formats de date, aux paramètres régionaux pour le client et le serveur, un grand potentiel de causer des dégâts).

je vous remercie pour votre intervention

[Serge_G 90]

  On 6/28/2023 at 1:21 PM, Analyste2023 said:

bonjour a tous ,merci a vous de m'avoir aider  vos intervention m'en beaucoup aider dans l'avancement de mon projet je suis fier d'etre parmi vous et tres content d'utiliser delphi pour les applications windows

Il existe un très bon forum en français https://www.developpez.net/forums/f15/environnements-developpement/delphi/ (mon pseudo SergioMaster)

[Die Holländer 82]

If you use the TDateTimePicker component to select a date then take care of the Time part of the

component (see object inspector) because Delphi sneaky adds the current time when you drop

the component on the form.. If you have a time part also in the database table field then

be sure your SQL does the right query when doing things like <=, = , >= if the time part

of the component is set on 00:00:00. 

[programmerdelphi2k 238]

  On 6/29/2023 at 8:20 AM, Die Holländer said:

Delphi sneaky adds the current time when you drop

the component on the form.

for that, you has the "Date" property:

  Quote

  DateTimePicker1.Date;
  CalendarPicker1.Date;
  DatePicker1.Date;

 

[mvanrijnen 124]

I always use DateOf when assigning a TDate to a  date field in a sql query. 

It's sometimes hard to know if somehow a time part was added to a date value. So just to be sure i do a lot of:
(especially when the TDate field/param is in a SQL select statement)

 

procedure DoMyUpdate(const AMyDateParameter : TDate);
begin
   qry.ParamByName('MyDateParameter').AsDate := DateOf(AMyDateParameter);
   ...
 end;

 

or if needed (eg in a loop) extract the Date value outside the loop:

 

procedure DoMyUpdate(const AMyDateParameter : TDate);
var
  myRealDateVar	: TDate;
begin
  myRealDateVar := DateOf(AMyDateParameter);
  while i<cnt do
  begin
    ....
    ....
    qry.ParamByName('MyDateParameter').AsDate := myRealDateVar;
   inc(i);
   .....
end;

 

Edited June 30, 2023 by mvanrijnen