You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
For DateTime values, the generated SQL statement contains the formatted value in quotation marks. E.g.
dataContext.Orders.Where(o => o.OrderDate==new DateTime(2020,1,2));
generates SQL such as
SELECT * FROM "ORDERS" WHERE "ORDERDATE" = '2020-01-02 00:00:00.0000'
That does _not_ work in case the DateTime value is used as a parameter for a function/procedure. E.g.
dataContext.Orders.Select(o => new { Days = DbFunctions.DiffDays(o.OrderDate,new DateTime(2020,1,2) })
generates SQL such as
SELECT DATEDIFF(DAY, "ORDERDATE", '2020-01-02 00:00:00.0000') AS "DAYS" FROM "ORDERS"
results in the following error message: "expression evaluation not supported. Expected DATE/TIME/TIMESTAMP type as first and second argument to DATEDIFF"
The solution is quite simple - explicitly casting as TIMESTAMP in SQL (SqlGenerator.cs):
internal static string FormatDateTime(DateTime value)
{
var result = new StringBuilder();
result.Append("CAST(");
result.Append("'");
result.Append(value.ToString("yyyy-MM-dd HH:mm:ss.ffff", CultureInfo.InvariantCulture));
result.Append("' AS TIMESTAMP)");
return result.ToString();
}
internal static string FormatTime(DateTime value)
{
var result = new StringBuilder();
result.Append("CAST(");
result.Append("'");
result.Append(value.ToString("HH:mm:ss.ffff", CultureInfo.InvariantCulture));
result.Append("' AS TIME)");
return result.ToString();
}
The resulting SQL will be as follows:
SELECT DATEDIFF(DAY, "ORDERDATE", CAST('2020-01-02 00:00:00.0000' AS TIMESTAMP)) AS "DAYS" FROM "ORDERS"
I don't know any cases where an explicit cast (in comparison to the current implementation) should be an issue.
Submitted by: Daniel Richter (drichter)
For DateTime values, the generated SQL statement contains the formatted value in quotation marks. E.g.
dataContext.Orders.Where(o => o.OrderDate==new DateTime(2020,1,2));
generates SQL such as
SELECT * FROM "ORDERS" WHERE "ORDERDATE" = '2020-01-02 00:00:00.0000'
That does _not_ work in case the DateTime value is used as a parameter for a function/procedure. E.g.
dataContext.Orders.Select(o => new { Days = DbFunctions.DiffDays(o.OrderDate,new DateTime(2020,1,2) })
generates SQL such as
SELECT DATEDIFF(DAY, "ORDERDATE", '2020-01-02 00:00:00.0000') AS "DAYS" FROM "ORDERS"
results in the following error message: "expression evaluation not supported. Expected DATE/TIME/TIMESTAMP type as first and second argument to DATEDIFF"
The solution is quite simple - explicitly casting as TIMESTAMP in SQL (SqlGenerator.cs):
internal static string FormatDateTime(DateTime value)
{
var result = new StringBuilder();
result.Append("CAST(");
result.Append("'");
result.Append(value.ToString("yyyy-MM-dd HH:mm:ss.ffff", CultureInfo.InvariantCulture));
result.Append("' AS TIMESTAMP)");
return result.ToString();
}
internal static string FormatTime(DateTime value)
{
var result = new StringBuilder();
result.Append("CAST(");
result.Append("'");
result.Append(value.ToString("HH:mm:ss.ffff", CultureInfo.InvariantCulture));
result.Append("' AS TIME)");
return result.ToString();
}
The resulting SQL will be as follows:
SELECT DATEDIFF(DAY, "ORDERDATE", CAST('2020-01-02 00:00:00.0000' AS TIMESTAMP)) AS "DAYS" FROM "ORDERS"
I don't know any cases where an explicit cast (in comparison to the current implementation) should be an issue.
Commits: a963782
The text was updated successfully, but these errors were encountered: