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 ?
