SQL CLR and nullable datetime parameter

Question

I'm trying to write a SQLCLR function that takes a DateTime2 as input and returns another DateTime2. Based on this post I altered the parameter to be the C# type DateTime giving me the level of precision I require. However because the input can be null I would like it to be DateTime?; the return type as well.

using System;
using Microsoft.SqlServer.Server;

namespace SqlServer.Functions {
    public class UserDefinedFunctions {
        [SqlFunction(DataAccess = DataAccessKind.None)]
        public static DateTime? GetLocalTimeFromGMT(DateTime? dateTime) {
            if (dateTime.HasValue)
                return DateTime.SpecifyKind(dateTime.Value, DateTimeKind.Utc).ToLocalTime();
            else
                return (DateTime?)null;
        }
    }
}

The problem is I get the following error when I try to deploy:

Error 1 Cannot find the type 'Nullable`1', because it does not exist or you do not have permission. SqlServer.Functions

I'm using Sql Server 2008 and Visual Studio 2008.

Answer 1

Unfortunately Visual Studio 2008 does not support deployment of SQLCLR functions with many of the new features added in SQL Server 2008, including Nullable Types. You can see the Connect item Bob Beauchemin filed on this issue, which has been fixed for Visual Studio 2010.

There a workaround listed in the Connect item which indicates to Visual Studio that it can use the System.Core and System.Xml.Linq assemblies in SQLCLR, which should work for you. Otherwise you can deploy the function to the database manually.