SQLCLR and DateTime2

Question

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

I'm trying to write an SQLCLR function that takes a DateTime2 as input and returns another DateTime2. e.g. :

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 fine. I want these SqlDateTimes to map to SQL Server's DateTime2, so I try to run this T-SQL :

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

This gives the following error :

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

Using DATETIME (instead of DATETIME2) works fine. But I'd rather use DATETIME2 to support the increased precision. What am I doing something wrong, or is DateTime2 not (fully) supported by SQLCLR ?

Answer 1

Note that using "DateTime?" still always gives build errors (even in VS 2013 with sql 2012), though apparently the result is usable if one selects "build, deploy" and then uses the files in the obj folder, editing the generated.sql file in the sql query window (to use DateTime2 as parameter) before executing to add it to Sql Server.
The build error is "SQL46010: Incorrect syntax near )." in \obj\Debug\YourPrjName.generated.sql

(Would post above as comment if I could.)

Answer 2

You need to change the DateTime types in the signature of your Function Method. SQLDateTime maps to a DateTime on the database.

System.DateTime is more precise and can be mapped to DateTime2 (but by default, it'll be dropped as a DateTime in the deploy script).

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

Then you can tweak your deploy script to read.

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

Running your function should now give you more precise output.

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