How trunc datetime field to date with Aurelius

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. :)