Date Time with filter in FDTable

[limelect 47]

Return to my filter but with date and time

My basic statement that worked is

ProjectsFDTable.Filter :='MyDateTime = {d ' +FormatDateTime('yyyy-mm-dd ',AdvDateTimePicker2.Date)+'}';

The problem 

I want to filter only date AdvDateTimePicker2.Date BUT MyDateTime has date and time. !!!

 

I know of {fn CONVERT(MyDateTime,DATE)} I did not know where to place it in my basic function.

 

My solution that works (BUT I DO NOT LIKE IT) is:

ProjectsFDTable.Filter :='MyDateTime > {dt ' +FormatDateTime('yyyy-mm-dd ',AdvDateTimePicker2.Date)+' 00.00.00 }'+
 ' and MyDateTime < {dt ' +FormatDateTime('yyyy-mm-dd ',AdvDateTimePicker2.Date)+' 23.59.59 }';
 

Which is date+time > and date+time < in beetwin.

 

Any better idea?

In SQL the above {fn CONVERT(MyDateTime,DATE)} works.

 

 

[ergeka 5]

You could try DateOf( AdvDateTimePicker2.Date) to get the date only.

[limelect 47]

@ergeka I do not understand. The problem is "MyDateTime"

which I have to get from it the date only.

AdvDateTimePicker2.Date has nothing to do with the problem.

AdvDateTimePicker2.Date has DATE only

Edited November 6, 2021 by limelect

[Hans J. Ellingsgaard 19]

If you use a FDQuery instead of FDTable, you could cast your datetime field to date. Then you can filter on that date field.

[limelect 47]

@Hans J. Ellingsgaard read last line of my topic.

[Serge_G 85]

I think the problem is deeper. 

Using a FDquery (with parameter) instead of FDTable is for me the best solution, assuming your SGBD is MySQL

SELECT * FROM mytable where DATE(MyDateTime)=:paramdate

by then instead of a filter building and activating you only have to write something like 
 

FdQuery.Open('',[AdvDateTimePicker2.Date]);

Contraction of
 

fdquery.Close;

fdquery.prambyname('paramdate').asDateTime:= AdvDateTimePicker2.Date ;

fdquery.Open;

Note to avoid flickering, surround the code with a fdQuery.DisableControls / Fdquery.EnableControls

 

Another way if you want to use FDtable and Filter is to add to your  DBtable (MySQL I assume) a generated column for example

ALTER TABLE mytable ADD c_date integer AS (YEAR(mydate)*10000+MONTH(mydate)*100+DAY(mydate))

now you can use this computed column for your filter  

 

var dd,mm,yy : word;
begin
  decodedate(advdatetimePicker2.date,yy,mm,dd);
  fdtable.filtered:=false; 
  fdtable.filter:=Format('c_date=%d',[yy*10000+mm*100+dd]);
  fdtable.filtered:=true;
  

 

[Fr0sT.Brutal 897]

Check if FDTable has any date-related built-in functions allowed in filters. If not, your solution is OK. Just beware of locale-defined date formats (refer to filter specs to find whether it uses fixed or locale-dependent format). You can also shorten the range construction: `(timefield >= dt) and (timefield < IncDay(dt, +1)`

[limelect 47]

@Serge_G As per your suggestion with D10.2.3

 

  ProjectsFDTable.Filtered := false;
  ProjectsFDTable.FilterOptions := [foCaseInsensitive];

   decodedate(advdatetimePicker2.date,yy,mm,dd);
   ProjectsFDTable.filter:=Format('MyDateTime=%d',[yy*10000+mm*100+dd]);

from f7 'MyDateTime=20211003'  for 03/10/2021

 

Do not forget MyDateTime has date and time in it

Edited November 8, 2021 by limelect

[Serge_G 85]

 

Please read all my post before posting this! I forgot nothing, you don't follow my steps.

 

You miss the :
 

Quote

 

Another way if you want to use FDtable and Filter is to add to your  DBtable (MySQL I assume) a generated column for example

ALTER TABLE mytable ADD c_date integer AS (YEAR(mydate)*10000+MONTH(mydate)*100+DAY(mydate))

now you can use this computed column for your filter  

 

It was an example, not involving date format.  And this is SGBD dependent.

 

So, it's not your field MyDateTime which is involved in the filter I wrote, but a computed column  I named c_date, computed column you have to add to your table. You jump to a bad conclusion.

 

Filter expression is not preprocessed I think,  so you can't use {fn CONVERT(mydate,DATE)}

 

 

Edited November 9, 2021 by Serge_G

[Hans J. Ellingsgaard 19]

One way to get around that datetime field, is to create a View on the datbase, where you cast your datetime field to a date field, and then connect the table component to the view instead of the query.

 

Is there a specific reason why you want to use a table compnent instead of a query?

[limelect 47]

@Hans J. Ellingsgaard It is a fairly large program with many sections

Some with SQL and some table.

I did not want to start to redesign a large portion (maybe stupid but factual).

 

@Serge_G Thanks