Delphi FireDAC TFDQuery SQL Server DataTime filter problem

Question

For the life of me, it seems I can't filter my TFDQuery using a DateTime field/format. I have this query that needs to "import" filters from a component (cxGrid), among them are:

• "Date = " (DateTimeField);
• "Value = " (FloatField);
• "Supplier = " (StringField);

(And a few others, but those are the more important ones). Date is seconds sensitive, so it has HH:MM:SS, this is necessary for stock/contability purposes.

Now what I mean about "importing" filters is that I'm using the same filters as the ones current marked on a cxGrid. I do so using this simple method:

Filter := cxGridDBTableView.DataController.Filter.FilterText;

Which gives me a string of all the filters currently in use in that cxGrid.

I then toss this string in the filters of my query as in:

MyTFDQuery.Filtered := False;

MyTFDQuery.Filter := Filter;

MyTFDQuery.Filtered := True;

This posed a problem with Value, as we had a display format changing the float to a different format (from, let's say, 2.4 to 2,40) but this has already been fixed. Supplier and the other fields all worked like a charm, mostly because they didn't have a mask/display format.

My problem is when running the DateTime field (Date) filter, I get this error:

[FireDAC][Stan][Eval]-114. Expected []

This field has no display formats or masks of any kind.

I did try searching for this error code online, and couldn't find anything. I did try to change the format of the Date filter, seeing as many people had issues with the format. I have tried all forms of:

MyTFDQuery.Filter := 'Date = ''2021-01-01 10:00:00'''

MyTFDQuery.Filter := 'Date = {dt 2021-01-01 10:00:00}'

MyTFDQuery.Filter := 'Date = ' + Chr(39) + '2021-01-01 10:00:00' + Chr(39)

MyTFDQuery.Filter := 'Date = {CONVERT(''2021-01-01 10:00:00'', DATETIME)}

and many others that I can't recall from the top of my head, right now.

And also tried several different formats for the date... Such as:

YYYY-MM-DD
YYYY-DD-MM
DD-MM-YYYY
MM-DD-YYYY

Or even the separators for that date. I've tried ' - '; ' / '; ' . ' and the such (like 24.02.1982 instead of 24/02/1982). and kept on trying different ways to make this work, with different combinations. When I searched about this issue online, specially in "how to filter a dataset/tfdquery by datetime", most people were being told to use different formats for the date/datetime to make it work. It seemed everyone had a different solution for this.

I'm not sure what to try anymore. Is this somehow connected to regional settings, as one search result pointed out? Where would those settings be from? The Delphi IDE? FireDAC? The DB I use? Is there something I'm missing?

Basically I want to know what format I should be using to filter my TFDQuery.

Thank you all for reading so far, any help would be highly appreciated. I hope the question is straightforward enough.

PS: I'm sorry for anything wrong I made have done in the making of this question. English is not my native language, and it's been a really long week. Thank you for the patience.

UPDATE: I've identified the error with a lot of guesswork. The milliseconds bit of the datetime is being taken into account when the filter is going to be applied to the dataset, and the cxGrid component would round the seconds (e.g changing '2021-02-01 18:05:03.822' to '2021-02-01 18:05:04').

That way, when the Date = '2021-02-01 18:05:04' would filter something, it wouldn't show up anything. It wasn't an issue with formatting, but rather with equality of datetimes.

To fix this, I followed Brian's advice and made the change:

cxGridDBTableView.DataController.Filter.DateTimeFormat := 'yy-mm-dd hh:mm:ss.zzz'

Now the millisecs are being taken from the filter, and the rounding ignored. Unfortunatelly I'm stuck on the error it gives:

[FireDAC][Stan][Eval]-118. Couldn't convert variant of type (UnicodeString) into type (Date)

Any further help would be highly appreciated.

Answer 1

I've found the "solution" to this problem. I simply assumed the query didn't accept the string filter with milliseconds, maybe some compatibility issues. I'm leaving this here in hopes that someone with a problem like mine can solve it fast, and maybe someone else can improve on this and make it more elegant/efficient.

I broke the string and inserted in a stringlist for easy management:

MyStringListFilters.LineBreak := ' AND ';
MyStringListFilters.Text := Filter;

This would turn a filter's string from:

((Date = ''28/09/2022 18:22:44.789'') OR (DATE = ''11/02/2019 07:18:03.122'')) AND ((Value = 2,4) OR (Value = 7,9)) AND (SUPPLIER = ''My Supplier Name Ltda'')

To:

((Date = ''28/09/2022 18:22:44.789'') OR (DATE = ''11/02/2019 07:18:03.122''))
((Value = 2,4) OR (Value = 7,9))
(SUPPLIER = ''My Supplier Name Inc'')

Now fixing the values is easy by just using a StringReplace:

While i < MyStringListFilters.Count do
  if (Copy(MyStringListFilters[i], 2, 5) = 'Value') or (Copy(MyStringListFilters[i], 3, 5) = 'Value') then
    StringReplace(MyStringListFilters[i], ',', '.', [rfReplaceAll]);

(When there is two or values, there will be an extra parentheses in front of the string).

Fixing the Date was a bit harder. I had to insert the string inside another string list, and break it up the same way:

//inside of the same while
if (Copy(MyStringListFilters[i], 2, 4) = 'Date') or (Copy(MyStringListFilters[i], 3, 4) = 'Date') then
begin
  MyStringListDates.LineBreak := ' OR ';
  MyStringListDates.Text := MyStringListFilters[i];
//it keeps on going...

Which further breaks the date string to:

((Date = ''28/09/2022 18:22:44.789'')
(Date = ''11/02/2019 07:18:03.122''))

Be aware: We have to remove the parentheses in the first filter and in the last as well if there is two or more Date filters. We also have to set things up as it was by the end of the loop. Do remember to always increment the index integer of both whiles.

//This happens inside the first while...
while j < MyStringListDates.Count do
begin
  TempDate := Copy(MyStringListDates[j], 10, 19);
  //This pulls the whole second, ignoring milliseconds from the DB.
  MyStringListDates[j] := '((Date >= ' + QuotedStr(TempData) + ') and (Date < ' + QuotedStr(DateTimeToStr(IncSecond(StrToDateTime(TempData)))) + '))';
  FixedDate := FixedDate + MyStringListDates[j];
  if FixedDate = '' then
  begin
    // Puts the first parentheses back.
    if MyStringListDates.Count > 1 then
       FixedDate := '(';
       FixedDate := FixedDate + MyStringListDates[j];
   end
   else
     FixedDate := FixedDate + ' OR ' + MyStringListDates[j];
   Inc(j);
end
//Remember to add the last parentheses here, if there is more than 1 Date Filter.

I would like to thank @Brian for their help with this solution. I didn't notice the component could be changed so the DateTime would come differently.

I would also like to thank @marc_s for editing the original question. This is my first question on the platform ever, so I didn't know what I was doing (Still don't. Sorry).

Also a special thanks to @Uwe Raabe, I've also used their answer on how to split strings using a multiple character delimiter on this problem. Here's the link to that question they answered:

How to split string by a multi-character delimiter?