Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Explicit CAST for Timestamp values [DNET932] #855

Closed
firebird-automations opened this issue Mar 13, 2020 · 6 comments
Closed

Explicit CAST for Timestamp values [DNET932] #855

firebird-automations opened this issue Mar 13, 2020 · 6 comments

Comments

@firebird-automations
Copy link

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

@firebird-automations
Copy link
Author

Commented by: @cincuranet

Yeah, looks like a safe change to me too. Care to do a PR?

@firebird-automations
Copy link
Author

Commented by: Daniel Richter (drichter)

All changes are mentioned above, but if you want me to create a PR of course I can create one.

Which one is the "correct" repo to create a PR? FirebirdSQL/NETProvider or cincuranet/FirebirdSql.Data.FirebirdClient?

@firebird-automations
Copy link
Author

Commented by: @cincuranet

cincuranet/FirebirdSql.Data.FirebirdClient is easier for me.

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Author

Commented by: Daniel Richter (drichter)

cincuranet/FirebirdSql.Data.FirebirdClient#93

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: In Progress [ 3 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: vNext [ 10920 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants