SQLCLR and DateTime2
- by Moe Sisko
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 ?
