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 ?

score 10 · Accepted Answer · answered Apr 12 '10 at 09:05

10

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

answered Apr 12 '10 at 09:05

Eoin Campbell

43,500
17
101
157

2

Thanks for that. Does this technique support passing and returning NULLs ? – Moe Sisko Apr 12 '10 at 23:57
How can I tweak a deploy script? There isn't one as far as I'm away when you right click and choose "Deploy" in visual studio. The whole point of the SqlFunction attribute is to automate that process so you can just choose deploy from the right click menu. – Triynko Feb 26 '13 at 23:26
And no... this doesn't support nulls, since you'd have to use Nullable, which doesn't seem to be supported. It fails to deploy even in Visual Studio 2010 Ultimate. – Triynko Feb 26 '13 at 23:46
@MoeSisko: Yes, NULLs are supported by this. You just need to use "DateTime?" instead of "DateTime". I have tested this for both input parameters and the return type and the compiled code works in both SQL Server 2008 (tested on R2) and 2012. Since DateTime is not a SQL type, you would test for nulls via "== null" rather than ".IsNull" like with the SQL types. – Solomon Rutzky May 01 '13 at 16:55
@Triynko: Yes, NULLs are in fact supported. I just gave details in my prior comment to Moe but cannot only tag one person per comment. – Solomon Rutzky May 01 '13 at 16:56
@Triynko: Regarding deployment, the [SqlFunction] attribute is to indicate compile options to the compiler as well as assist in creating the deployment SQL. If you are using a more recent version of Visual Studio, the "Publish" process should leave a SQL script in your obj\\(active_config) folder named "PROJECT.NAME.generated.sql" if "Generate DDL" is checked in the SQLCLR tab of Project Properties. Sadly it does't like "DateTime?" and shows "unsupported type" :(. But not a big deal if you had to edit that anyway to be DATETIME2 instead of DATETIME. – Solomon Rutzky May 01 '13 at 17:08

score 1 · Answer 2 · answered Jan 13 '14 at 02:26

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.)

SQLCLR and DateTime2

2 Answers2

Linked