SQL Server Query: DateTime addition/subtraction translation to server

[smitpatel]

Addition/subtraction of DateTime gives type of TimeSpan. At present postgre can do it correctly because it has mapping type - interval.

This issue is to track,

• Translation of addition/subtraction for DateTime to server and materializing it back to TimeSpan on client side doing some computation.
• Translation of member access on +/- of DateTime (e.g. (DateTime - DateTime).TotalDays) to server. For SQLite it may be possible to do using julianday function subtraction which gives difference of days in REAL type. Reading a TimeSpan will coerce the REAL value.

[bricelam]

We can't translate this on SQLite (without registering a UDF). If we rely on coercion, queries like this would fail:

var tickesOverSLA
    = from t in tickets
      where DateTime.Now - t.DateOpened > t.SLA
      select t;

[NetMage]

@bricelam Why won't the SQLite date and strftime functions work to translate the query?

[bricelam]

@NetMage They work just fine. (see PR #19617)

My comment was more that if we did this by default for all relational providers, the results would be wrong for SQLite.