How to map SQL CLR DateTime to datetime2

Question

I have SQL CLR UDF which returns a DateTime, but when I publish to SQL the generated SQL script is mapping it to datetime which loses precision over datetime2. Besides manually modifying the script (error prone!) each time I update the code, is there a way to specify that Visual Studio should map DateTime to datetime2? I am using the latest Visual Studio 11 Beta: Version 11.0.50214.1 BETAREL. It would be nice if there were an attribute along the lines of SqlFacet that I could add to my UDF definition in C#. I tried adding a SqlFacet with precision 7 (which would make sense, as datetime2 is aka datetime2(7)), but it failed. I also set the Type System Version to explictly be "SQL Server 2008" in the connection string.

Answer 1

Hello Partner,

As far as I kown that there is no equivalent for datetime2 SQL Server data type in the common language runtime (CLR) for SQL Server in the System.Data.SqlTypes or Microsoft.SqlServer.Types namespace, see this MSDN article for more information. So if you define your UDF in C# with the following code, once you publish it to the target SQL2008, you actually map DateTime to datetime.

[Microsoft.SqlServer.Server.SqlFunction]
    public static DateTime SqlFunction5()
    {
           return new DateTime(2012, 4, 16);
    }

One approach I can think out for you is that you can use T-SQL to alter the function in the PostDeployScript.sql file, maybe your script is similar to:

ALTER FUNCTION [dbo].[SqlFunctionName]()
RETURNS [datetime2] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SQLCLRProject].[UserDefinedFunctions].[SqlFunctionName]

Thanks.  

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Thanks, Vicky. Your workaround is reasonable and will make a good interim fix. I still believe that this should be fixed as, without the post-deployment script, there is a high chance of data loss for the unsuspecting developer. I've opened the following bug containg a few more details on Connect.

http://connect.microsoft.com/VisualStudio/feedback/details/736720/vs11-sql-clr-udf-maps-datetime-to-datetime-instead-of-datetime2

Answer 3

OK. I hope our senior PG members can help you out of your issue.

Have a nice day.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us