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: 753
        
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