Unable to match Datetime type when trying to publish SQL CLR database

I am trying to find an old solution that posts a number of .net functions for a SQL Server database. But trying to post to a new database doesn't work on a function that manipulates dates.

Function that doesn't work:

[SqlFunction(TableDefinition="localtime datetime2", IsDeterministic=true, IsPrecise=true,
             DataAccess=DataAccessKind.None,
             SystemDataAccess=SystemDataAccessKind.None)]
public static DateTime ConvertFromUTC(DateTime utctime, string timezoneid)
{
    if (utctime.Kind == DateTimeKind.Unspecified)
        utctime = DateTime.SpecifyKind( utctime, DateTimeKind.Utc );

    utctime = utctime.ToUniversalTime();

    return TimeZoneInfo.ConvertTimeBySystemTimeZoneId( utctime, timezoneid );
}

      

The error message I get when trying to post is the following:

Creation of [dbo]. [ConvertFromUTC] ...

(268,1): SQL72014: .Net SqlClient Data Provider:

Msg 6551, Level 16, State 2, Procedure ConvertFromUTC, Line 1
CREATE FUNCTION for "ConvertFromUTC" failed because the T-SQL and CLR types for the return value do not match.

(268,0): SQL72045: Script runtime error

SQL generated from .net in an attempt to add a function:

CREATE FUNCTION [dbo].[ConvertFromUTC]
    (@utctime DATETIME, @timezoneid NVARCHAR (MAX))
RETURNS TABLE ([localtime] DATETIME2 (7) NULL)
AS EXTERNAL NAME [database].[IntelligentTutor.Database.Functions].[ConvertFromUTC]

      

The SQL definition for the version of the function in the existing database (which confirms that @MattJohnson was right about how it needs to be fixed):

CREATE FUNCTION [dbo].[ConvertFromUTC]
    (@utctime [datetime], @timezoneid [nvarchar](4000))
RETURNS [datetime] WITH EXECUTE AS CALLER
AS EXTERNAL NAME [database].[IntelligentTutor.Database.Functions].[ConvertFromUTC]

      

+3


source to share


1 answer


The SQL function does not match the .NET method signature. To make it match:

  • Change the type @utctime

    to DATETIME2

    instead of DATETIME

    in the function definition.

  • Change the return type to only RETURNS DATETIME2

    instead of returning a table with a null datetime2 column.



Also note that if you are using SQL 2016 or later, or in Azure SQL DB, you do not need this feature as you can now use AT TIME ZONE

.

+3


source







All Articles