Dear all,
I try using Aurelious & XData to migrate an application from Microsoft SQL server to Interbase 2020 and I'm having an issue with the datetime (timestamp) fields.
It seems as if the date and time are not being converted correctly using Linq, resulting in the desired queries not being returned.
I use the followings
var Criteria : TCriteria;
Criteria := FManager.Find;
...
Filter := (Linq['DTCreated'] >= aFrD) and (Linq['DTCreated'] <= aToD);
Criteria.Add(Filter);
...
MyList := Criteria.OrderBy('ID').List; <-- At this point the returned list is Unassigned.
Do you have any suggestions on how I can solve the problem?
What do you mean by Unassigned? The list is nil? Or empty?
Do you have any suggestions on how I can solve the problem?
First thing is to use OnSqlExecuting event to inspect the generated SQL and check the parameter values and types and see if they make sense.
Then try to execute such SQL separately,, outside Aurelius, directly with the database component, using the same SQL, same parameter types and values, and see if the behavior is the same.
Dear Wagner,
the returned list is nil.
I did tests with generated SQL as you suggested and it seems that the problem is in the format of the parameter dates.
SQL Statement :
SELECT .... WHERE (A.DTCreated >= :p_0 And A.DTCreated <= :p_1) ORDER BY A.ID Asc
When try to execute the SQL statement, in the Interbase Interactive SQL tool, I get the message " conversion error from string "30/11/2023 23:59:59" ".
When I change the parameter from "30/11/2023 23:59:59" to "2023-11-30 23:59:59" the above statement runs well.
Is there a setting I can change for the date format?
This never happens. Aurelius always return a valid list, no matter what. If there are no returned records, the list is empty, but still not nil.
The date format doesn't matter, Aurelius doesn't replace the text inside SQL, instead if uses the underlying database component (FireDAC, UniDAC, etc.) and set the parameters using the proper methods.