SQLCLR and DateTime2

Question

Using SQL Server 2008, Visual Studio 2005, .net 2.0 SP2 (support for new SQL Server 2008 data types).

I am trying to write a SQLCLR function that takes DateTime2 as input and returns another DateTime2. eg

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace MyCompany.SQLCLR
{
    public class DateTimeHelpCLR
    {
        [SqlFunction(DataAccess = DataAccessKind.None)]
        public static SqlDateTime UTCToLocalDT(SqlDateTime val)
        {
            if (val.IsNull)
                return SqlDateTime.Null;

            TimeZone tz = System.TimeZone.CurrentTimeZone;
            DateTime res = tz.ToLocalTime(val.Value);

            return new SqlDateTime(res);
        }
    }
}

Now the above compiles in order. I want these SqlDateTimes to appear in SQL Server DateTime2, so I am trying to run this T-SQL:

CREATE function hubg.f_UTCToLocalDT
(
    @dt DATETIME2
)
returns DATETIME2
AS
EXTERNAL NAME [SQLCLR].[MyCompany.SQLCLR.DateTimeHelpCLR].UTCToLocalDT
GO

This results in the following error:

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

DATETIME ( DATETIME2) . DATETIME2 . - , DateTime2 () SQLCLR?

Answer 1

DateTime Function. SQLDateTime DateTime .

System.DateTime DateTime2 ( DateTime script).

[SqlFunction(DataAccess = DataAccessKind.None)]
//OLD Signature public static SqlDateTime UTCToLocalDT(SqlDateTime val) 
public static DateTime UTCToLocalDT(DateTime val) {
   ...
}

script .

CREATE FUNCTION [UTCToLocalDT]
(
    @dt [datetime2]
)
RETURNS [datetime2]
AS
    EXTERNAL NAME [SQLCLR].[MyCompany.SQLCLR.DateTimeHelpCLR].UTCToLocalDT
GO

.

DECLARE @input DateTime2, @output DateTime2
SET @input = '2010-04-12 09:53:44.48123456'
SET @output = YourDatabase.dbo.[UTCToLocalDT](@input)
SELECT @input, @output

Answer 2

, "DateTime?" ( VS 2013 sql 2012), , , , ", ", obj, .sql sql ( DateTime2 ) , Sql Server.
: "SQL46010: " ). \obj\Debug\YourPrjName.generated.sql

( , .)