how to use SQL Server Expressions/Functions in a Tfdquery filter in Delphi

Question

I have the following situation, I need to check if the time of a given task is no longer filled in the system and for this by performing an SQL command in SQL Server 2014 works, as in the example

Select (cast((convert(datetime,hora,108)) +'00:01:00.000' as time(7))) as tempo from MIGRACOES_INTE_HORA

OBS: I perform the Convert to transform the time of Varchar for team...

but in the system I use the filter property of a TFDQuery to perform the query, more to optimize the same.

in the added filter

(CAST((convert(datetime,'+TimeToStr(dtpInteHora.Time)+',108)) + '00:01:00.000', as time(7)) = '+DateToStr(qryInteHora.FieldByName('Hora').AsDateTime)+') or
(CAST((convert(datetime,'+TimeToStr(dtpInteHora.Time)+',108)) - '00:01:00.000', as time(7)) = '+DateToStr(qryInteHora.FieldByName('Hora').AsDateTime)+')

but when active the filter comes the following error

[FireDAC][Stan][Eval]-100. Column or function [CAST] is not found. Hint: if the name is a function name, then add FireDAC.Stan.ExprFuncs to uses clause.

Would someone like to tell me how to make this filter??

Answer 1

Friend,

I searched here fast, but could not get confirmation, however, I believe that the Tfdquery Filter is applied only to the record already loaded from your Dataset. It does not apply this directly to your bank. Your queries are limited.

In your case, I believe you will have to apply the filter as query query, so that your bank is responsible for this.