NHibernate DateTime查询，由字符串格式引起的溢出异常 [英] NHibernate DateTime for query, Overflow exception caused by string format

问题描述

NHibernate正在生成Firebird不支持的以下SQL;

NHibernate is generating the following SQL which is not supported by Firebird;

where  (struct_cas0_.DELETED IS NULL)
       and struct_cas0_.ACCOUNT_ID = 372 /* @p0 */
       and struct_cas0_.DATE_RECORD <= '2005-01-01T00:00:00.00' /* @p1 */
       and struct_cas0_.DATE_RECORD >= '2006-12-31T00:00:00.00' /* @p2 */

以上SQL在Firebird中失败，并显示错误数据类型转换时发生溢出字符串转换错误2005-01-01T00：00：00.00

The above SQL fails in firebird with an error "Overflow occurred during data type conversion. Conversion error from string '2005-01-01T00:00:00.00'"

如果我们删除来自查询的T，Firebird执行查询没有问题;

If we remove the 'T' from the query, Firebird executes the query without problem;

where  (struct_cas0_.DELETED IS NULL)
       and struct_cas0_.ACCOUNT_ID = 372 /* @p0 */
       and struct_cas0_.DATE_RECORD <= '2005-01-01 00:00:00.00' /* @p1 */
       and struct_cas0_.DATE_RECORD >= '2006-12-31 00:00:00.00' /* @p2 */

还有我们可以让NHibernate在将DateTime转换为可查询的字符串时删除'T'。

Is there a way we can have NHibernate remove the 'T' when converting DateTime to a queryable string?

一些补充问题现在在一些重新建立之后提出。看来FireBird不支持组合日期和格式的格式DateTime格式（ISO8601） http：//en.wikipedia .org / wiki / ISO_8601 具有时间辨别符（T）。这可以被证实，因为我不明白为什么数据库不能支持这样的标准（可排序）日期格式？

An additonal question is now raised after some reasearch. It appears that FireBird does not support the combined date and fime format DateTime format (ISO8601) http://en.wikipedia.org/wiki/ISO_8601 with a time discrimiator character ("T"). Can this be confirmed as I don't understand why a database would fail to support such a standard (sortable) date format?

如果T被删除，支持日期。

If the "T" is dropped, the date is supported.

推荐答案

NHibernate不会将参数转换为字符串;这是ADO.NET提供商的责任。

NHibernate does not convert parameters to strings; that's the ADO.NET provider's responsibility.

尝试以下操作：

var connection = new FbConnection(theConnectionString);
var command = connection.CreateCommand();
var parameter = command.CreateParameter();
command.CommandText = "select something from that_table where date_record = @p";
parameter.Name = "p";
parameter.Value = DateTime.Today;
command.Parameters.Add(parameter)
connection.Open();
command.ExecuteReader();

如果失败，您的错误。

这篇关于NHibernate DateTime查询，由字符串格式引起的溢出异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！