SQLCLR and DateTime2

Posted by Moe Sisko on Stack Overflow See other posts from Stack Overflow or by Moe Sisko
Published on 2010-04-12T07:39:16Z Indexed on 2010/04/12 7:43 UTC
Read the original article Hit count: 694

Filed under:
|
|
|
|

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 ?

© Stack Overflow or respective owner

Related posts about sqlclr

Related posts about sql-server-2008