Hi, I need get a field TDateTime datatype as TDate only. I'm using GraphQL with Aurelius and I needing group by as TDate.
Thanks.
Esteban
I found that SQL standard is supported using cast, but I don't know as do it with Aurelius using TCriteria. Is there something similar to cast for use in Aurelius with TCriteria ?
Thanks.
Esteban
Not built-in, but you can always create your own functions then use the SqlFunction projection.
However, I would suggest what I consider a simpler way, if you just want to group, use Year, Month and Day projections, and group by then in that order.
I would suggest to add .DateOf to the list of SqlProjections. I think is used very often and the Year/Month/Day combination is a little clumsy.
I personally think .DateOf is less portable and less flexible. Year, Month and Date return simple integer values (not dates) and can be used individually (group by year and month, for example).
About flexibility - you're right. But in my case we have a lot of queries that perform a query for just one day and instead of this (pseudo code - date is SQL formatted):
.Where((Linq['IssueDate'].DateOf = '2023-08-11');
I have to write something like this:
.Where((Linq['IssueDate'].Year = 2023) and (Linq['IssueDate'].Month = 8) and (Linq['IssueDate'].Day >= 10) and (Linq['IssueDate'].Day < 11)
This is just a suggestion of course. The reason I need to keep the time in the DB data is because each document must have a date and time of creation, but when doing operations we often limit od a single day/week,/month... etc.
anyway of the end of the topic - keep the good job you're doing with Aurelius, I love it :)
1 Like
I completely agree with this.
We need the date and time in almost all datetime fields.
But queries are very often only filtered on the date portion. This is very complicated with year/month/day.
Previously (before Aurelius) we always used SQL queries on datetime with "between" and we would like to simplify it now.
1 Like
Just our of curiosity, which database servers you (collective) are using?
How would DateOf be translated to the actual SQL statement, in the databases you are using (and Aurelius has to support MySQL, PostgreSQL, Oracle, SQLite, SQL Server, DB2, Firebird, Interbase, AbsoluteDB, etc.)
I would work with ISO8601 date and do the comparison as a string.
I think I'll write my own SQL function for myself.
We only use Microsoft SQL Server and PostgreSQL
That would be extremely inefficient, wouldn't it?
That's also what I have always used and have been used so far. Something like
TheDate := EncodeDate(2024, 3, 19);
{...}
.Where(
(Linq['IssueDate'] >= TheDate) and (Linq['IssueDate'] < IncDay(TheDate, 1))
)
1 Like
I have a central unit that automatically evaluates a fully configurable filter and uses it to build the corresponding Aurelius query.
It's a little more difficult to add additional things here.
But I'll think about something, since this (searching by date without time) happens quite often.
Here is an excerpt from the code
It's about MakeCriteria/MakeExpression in the line:
"else if AFunc = sff_Date"
{-----------------------------------------------------------------------------}
{ private AddCriteriaOperation<T> }
{-----------------------------------------------------------------------------}
class procedure TdmDatabase.AddCriteriaOperation<T>(const ACriteria: TCriteria<T>; LP: TLinqProjection; const AOp: String; AValue: Variant);
begin
if AOp = sfo_OrderBy then ACriteria.OrderBy( LP, (AValue <> 'DESC'))
else if AOp = sfo_Equal then ACriteria.Add( LP = AValue)
else if AOp = sfo_NotEqual then ACriteria.Add( LP <> AValue)
else if AOp = sfo_GreaterThan then ACriteria.Add( LP > AValue)
else if AOp = sfo_GreaterOrEqual then ACriteria.Add( LP >= AValue)
else if AOp = sfo_LessThan then ACriteria.Add( LP < AValue)
else if AOp = sfo_LessOrEqual then ACriteria.Add( LP <= AValue)
else if AOp = sfo_Like then ACriteria.Add( LP.Like( AValue))
else if AOp = sfo_InsLike then ACriteria.Add( LP.ILike( AValue))
else if AOp = sfo_IsNull then ACriteria.Add( LP.IsNull)
else if AOp = sfo_IsNotNull then ACriteria.Add( LP.IsNotNull)
else if AOp = sfo_StartsWith then ACriteria.Add( LP.StartsWith( AValue))
else if AOp = sfo_EndsWith then ACriteria.Add( LP.EndsWith( AValue))
else if AOp = sfo_Contains then ACriteria.Add( LP.Contains( AValue))
else if AOp = sfo_InsContains then ACriteria.Add( LP.Upper.Contains( AnsiUpperCase( AValue)))
else if AOp = sfo_In then ACriteria.Add( LP._In( StringToArrayOfVAriant( AValue)))
else if AOp = sfo_InSql then ACriteria.Add( Linq.Sql( TDBTools.ReplaceDBFields( TMappingExplorer.Get( sys.ModelName), '{'+LP.PropName+'} in ('+AValue+')')))
;
end;
{-----------------------------------------------------------------------------}
{ private AddExpressionOperation }
{-----------------------------------------------------------------------------}
class procedure TdmDatabase.AddExpressionOperation(var LinqExpression: TLinqExpression; LP: TLinqProjection; const AOp: String; AValue: Variant; First: Boolean = False);
var
Expr: TLinqExpression;
begin
if AOp = sfo_Equal then Expr := ( LP = AValue)
else if AOp = sfo_NotEqual then Expr := ( LP <> AValue)
else if AOp = sfo_GreaterThan then Expr := ( LP > AValue)
else if AOp = sfo_GreaterOrEqual then Expr := ( LP >= AValue)
else if AOp = sfo_LessThan then Expr := ( LP < AValue)
else if AOp = sfo_LessOrEqual then Expr := ( LP <= AValue)
else if AOp = sfo_Like then Expr := ( LP.Like( AValue))
else if AOp = sfo_InsLike then Expr := ( LP.ILike( AValue))
else if AOp = sfo_IsNull then Expr := ( LP.IsNull)
else if AOp = sfo_IsNotNull then Expr := ( LP.IsNotNull)
else if AOp = sfo_StartsWith then Expr := ( LP.StartsWith( AValue))
else if AOp = sfo_EndsWith then Expr := ( LP.EndsWith( AValue))
else if AOp = sfo_Contains then Expr := ( LP.Contains( AValue))
else if AOp = sfo_InsContains then Expr := ( LP.Upper.Contains( AnsiUpperCase( AValue)))
else if AOp = sfo_In then Expr := ( LP._In( StringToArrayOfVAriant( AValue)))
else if AOp = sfo_InSql then Expr := Linq.Sql( TDBTools.ReplaceDBFields( TMappingExplorer.Get( sys.ModelName), '{'+LP.PropName+'} in ('+AValue+')'))
;
if First then
LinqExpression := Expr
else
LinqExpression := LinqExpression or Expr;
end;
{-----------------------------------------------------------------------------}
{ private MakeCriteria<T> }
{-----------------------------------------------------------------------------}
class procedure TdmDatabase.MakeCriteria<T>(const ACriteria: TCriteria<T>; const AName, AFunc, AOp: string; AValue: Variant);
begin
if AFunc.IsEmpty then AddCriteriaOperation<T>( ACriteria, Linq[ AName], AOp, AValue)
else if AFunc = sff_Date then AddCriteriaOperation<T>( ACriteria, Linq.SqlFunction( 'dbo.DateString', TypeInfo( String), Linq[ AName]), AOp, copy( AValue, 1, 10))
else if AFunc = sff_Datetime then AddCriteriaOperation<T>( ACriteria, Linq[ AName], AOp, ISODateToDateTime( AValue))
else if AFunc = sff_Year then AddCriteriaOperation<T>( ACriteria, Linq[ AName].Year, AOp, AValue)
else if AFunc = sff_Month then AddCriteriaOperation<T>( ACriteria, Linq[ AName].Month, AOp, AValue)
else if AFunc = sff_Day then AddCriteriaOperation<T>( ACriteria, Linq[ AName].Day, AOp, AValue)
else if AFunc = sff_Hour then AddCriteriaOperation<T>( ACriteria, Linq[ AName].Hour, AOp, AValue)
else if AFunc = sff_Minute then AddCriteriaOperation<T>( ACriteria, Linq[ AName].Minute, AOp, AValue)
else if AFunc = sff_Second then AddCriteriaOperation<T>( ACriteria, Linq[ AName].Second, AOp, AValue);
end;
{-----------------------------------------------------------------------------}
{ private MakeExpression }
{-----------------------------------------------------------------------------}
class procedure TdmDatabase.MakeExpression(var LinqExpression: TLinqExpression; const AName, AFunc, AOp: string; AValue: Variant; First: Boolean = False);
begin
if AFunc.IsEmpty then AddExpressionOperation( LinqExpression, Linq[ AName], AOp, AValue, First)
else if AFunc = sff_Date then AddExpressionOperation( LinqExpression, Linq.SqlFunction( 'dbo.DateString', TypeInfo( String), Linq[ AName]), AOp, copy( AValue, 1, 10))
else if AFunc = sff_Datetime then AddExpressionOperation( LinqExpression, Linq[ AName], AOp, ISODateToDateTime( AValue), First)
else if AFunc = sff_Year then AddExpressionOperation( LinqExpression, Linq[ AName].Year, AOp, AValue, First)
else if AFunc = sff_Month then AddExpressionOperation( LinqExpression, Linq[ AName].Month, AOp, AValue, First)
else if AFunc = sff_Day then AddExpressionOperation( LinqExpression, Linq[ AName].Day, AOp, AValue, First)
else if AFunc = sff_Hour then AddExpressionOperation( LinqExpression, Linq[ AName].Hour, AOp, AValue, First)
else if AFunc = sff_Minute then AddExpressionOperation( LinqExpression, Linq[ AName].Minute, AOp, AValue, First)
else if AFunc = sff_Second then AddExpressionOperation( LinqExpression, Linq[ AName].Second, AOp, AValue, First);
end;
Yes, you are right. It seems to be much easier :-)
1 Like
I mostly use MySQL.
In SQL I use DATE() function,this function just removes the time part, example
2024-01-01 13:00:00 --> 2024-01-01
First, thank you for a better usage.
From my point of view, the easiest solution would be something like this (meta-code):
TheDate1 := EncodeDate(2024, 3, 19);
TheDate2:= EncodeDate(2024, 3, 30);
{...}
.Where(
(Linq['IssueDate'].Date >= TheDate1) and (Linq['IssueDate'].Date <= TheDate2)
)
I think is the most readable. The usage would be similar to .Year function of Aurelius
And why can't you just follow the suggestion and use it like this:
TheDate1 := EncodeDate(2024, 3, 19);
TheDate2:= EncodeDate(2024, 3, 31);
{...}
.Where(
(Linq['IssueDate'] >= TheDate1) and (Linq['IssueDate'] < TheDate2)
)
Because sometimes i need the left side to be TDate, I don't remember exactly right now, but there is some situations that require that (maybe example - compare two DB fields, just by date).
Anyway, for most cases your suggestion is OK. :)