Explicit CAST for Timestamp values
----------------------------------
Key: DNET-932
URL: http://tracker.firebirdsql.org/browse/DNET-932
Project: .NET Data provider
Issue Type: Bug
Components: Entity Framework
Affects Versions: 7.5.0.0
Reporter: Daniel Richter
Assignee: Jiri Cincura
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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
_______________________________________________
Firebird-net-provider mailing list
Firebird-net-provider@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
