Hi,

I'm using LLBLGen Runtime Framework 5.7.1 in adapter mode, targeting .NET 4.6 and MS SQL Server.

I get an ORMQueryExecutionException SqlDateTime overflow when calling a SQL Table-Valued UDF that uses parameters of SQL type Date.

Here is the UDF declaration:

CREATE FUNCTION Difference(@dat1 as date, @dat2 as date)
   RETURNS TABLE
AS
RETURN
(
   SELECT DATEDIFF(YEAR, @dat1, @dat2) as result
)

As you can see, the parameters are of SQL type Date, meaning pretty much any value is acceptable (https://docs.microsoft.com/en-us/sql/t-sql/data-types/date-transact-sql?view=sql-server-ver15).

Here is the call:

new LinqMetaData(adapter).Difference(new DateTime(1,1,1), DateTime.Now).First.result

Here is the Exception:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException HResult=0x80131500 Message=An exception was caught during the execution of a retrieval query: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. Source=SD.LLBLGen.Pro.ORMSupportClasses StackTrace: at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Boolean performValueProjectionsOnRawRow, Boolean postProcessDBNullValues, Dictionary2 typeConvertersToRun) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>c__DisplayClass22_0.<FetchProjection>b__0() at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteWithActiveRecoveryStrategy(Action toExecute) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression, Type typeForPostProcessing) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute[TResult](Expression expression) at System.Linq.Queryable.First[TSource](IQueryable1 source) at my code

Inner Exception 1: SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

The exception is unexpected, since the type of the parameter is Date and not DateTime.

Is this a bug, or am I doing something wrong?

Thanks!

andreas

I believe this is a .NET struct limitation, as the "System.Data.SqlTypes" has a minimum value of: 1/1/1753 for the SqlDateTime struct. So it's not any value as per the SQL Server .

yeah the function has 'Date' as type, and SqlDbType has a Date type, but the value is leading here for the parameter, and as the value is a DateTime, that type is used to determine the DbParameter type, which will be DateTime, not Date.

This is interesting. So indeed, the SqlDateTime Struct does not allowed values before January 1, 1753:

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqltypes.sqldatetime

However, I have no idea where this struct is used inside LLBLGen or ADO.NET. When declaring a parameter of an SqlCommand, its type is specified by a System.Data.SqlDbType Enum value, and this Enum does have a value SqlDbType.Date, that maps to the correct T-SQL type.

So in principle ADO.NET should support this. I did a few tests to make sure.

The following fails with the same exception as before, because the type is not explicitly specified to be Date:

var q = new SqlClient.SqlCommand();
var aq = new ActionQuery(q);
q.CommandText = "SELECT 1";
q.Parameters.AddWithValue("@d", new Date(1, 1, 1));
adapter.ExecuteActionQuery(aq);

The following works just fine:

var q = new SqlClient.SqlCommand();
var aq = new ActionQuery(q);
q.CommandText = "SELECT 1";
q.Parameters.Add("@d", SqlDbType.Date).Value = new Date(1, 1, 1);
adapter.ExecuteActionQuery(aq);

So shouldn't the LLBLGen UDF call support this as well?

acl wrote:

This is interesting. So indeed, the SqlDateTime Struct does not allowed values before January 1, 1753:

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqltypes.sqldatetime

However, I have no idea where this struct is used inside LLBLGen or ADO.NET. When declaring a parameter of an SqlCommand, its type is specified by a System.Data.SqlDbType Enum value, and this Enum does have a value SqlDbType.Date, that maps to the correct T-SQL type.

So in principle ADO.NET should support this.

Yes, that's what I said: SqlDbType does have a Date type, but the parameters created to call the function are created from the values specified, which are DateTime structs. This means our runtime will see the DateTime values and create parameters with type SqlDbType.DateTime, not SqlDbType.Date

At the moment there's no way to specify the type of a parameter in the mapping, so the system has to determine the parameter type based on the value that's passed in (in this case a DateTime value). It doesn't know anything about whether the type has to be 'Date', that's not currently available.

So it goes wrong because the value passed in is a DateTime, the engine can only convert that to a DateTime parameter, passes the value along and then it goes wrong inside SQL Server. It can't convert it to Date because it doesn't know if it has to do that: Date and DateTime as database type both convert to the System.DateTime .net type.

If you can, you can change the function to:

CREATE FUNCTION Difference(@dat1 as date, @dat2 as date)
   RETURNS TABLE
AS
RETURN
(
   SELECT DATEDIFF(YEAR, COALESCE(@dat1, N'1-1-1'), @dat2) as result
)

(not tested, but the idea is to pass in NULL for min-date instead of 1-1-1, so you get the max year diff, as that's what you're after I think)

Ok thanks for clarifying. So LLBLGen has no knowledge of the SQL type of the UDF parameters then? This is somewhat inconsistent with the behavior of linq queries on tables. There it knows the types of columns involved in comparisons pretty well (an older post of mine comes to mind: https://www.llblgen.com/tinyforum/Thread/24393/1). So doing this for UDF parameters as well might make things more consistent

Btw. The idea was to use Date to be robust in case of bad user inputs. So it's not just for the min value, but for any bad value a user might type. We'll have to work around that in some other way.

acl wrote:

Ok thanks for clarifying. So LLBLGen has no knowledge of the SQL type of the UDF parameters then? This is somewhat inconsistent with the behavior of linq queries on tables. There it knows the types of columns involved in comparisons pretty well (an older post of mine comes to mind: https://www.llblgen.com/tinyforum/Thread/24393/1). So doing this for UDF parameters as well might make things more consistent

With queries it knows the types as it knows via the mappings of the fields the target fields they're mapped on and the types, so it can make a good decision based on that. The function mappings don't have that. If you pass a field in a function call, it uses that type for the parameter, but as you pass a constant, it can only use that value as it has no other information.

Btw. The idea was to use Date to be robust in case of bad user inputs. So it's not just for the min value, but for any bad value a user might type. We'll have to work around that in some other way.

Hmm, it could still work, as sql server has an implicit conversion between datetime and date, however the min-date for Date is 1-1-1 and the min-date for DateTime is some vague date in 1789 or so (what epoch started around that time? French revolution? ), so if you want to mark one of the inputs as 'start' or 'end', you could use DateTime.MinValue instead, unless your dataset has valid input with dates around that time