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]
source to share
The SQL function does not match the .NET method signature. To make it match:
-
Change the type
@utctime
toDATETIME2
instead ofDATETIME
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
.
source to share