[Search Lists] Date conversion function not working

Original Post: Kirsty 08/10/2013 10:18:19

"I am creating a SQL query that uses dates and am trying to utilise the UTCToLocalTime / LocalTimeToUTC SQLfunctions. However, when I do I get the following message:
Msg 6512, Level 16, State 27, Line 1 Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80004005. You need to restart SQL server to use CLR integration features.
We have restarted the server, checked the memory and checked the .NET setup and cannot resolve it. All other custom date related functions seem to work. I also get the error message if I try to script out the function.
Please can anyone either a) suggest why it might not work / how to fix it or b) provide the script for the function so I can look into it myself."

Responses:

Kirsty 08/10/2013 12:03:09
"Thanks to technical@Grindeysfor the following responses:
Hi kirsty,

Tried logging in to respond to this, but for some reason I couldn’t get to this post on their site.

You have probably already tried this but it looks like them functions are Extended functions and so use the CLR runtime, by default this is disabled. Might be worth trying to disable and re-enable it just in case the setting has gone funny on your sever.
There are some Microsoft links on how to do it just in case. http://technet.microsoft.com/en-us/library/ms131048.aspx
On the above link it does mention that memory and processors could be a problem when loading .NET runtime so gives anther flag to add, might be worth a go depending on your server setup.

Computers configured with large amounts of memory and a large number of processors may fail to load the CLR integration feature of SQL Server when starting the server. To address this issue, start the server by using the -g memory_to_reserve SQL Server service startup option, and specify a memory value large enough.
http://msdn.microsoft.com/en-us/library/ms254506(v=vs.80).aspx

My Response:
Thanks so much for that Phil. Unfortunately we have already tried all that. You couldn't paste the function code over to me so I could have a look could you?

Many thanks

technical@Grindeysresponse:
With this being an extended .net procedure I think it will use to 2 datetime functions

DateTime.ToLocalTime() and DateTime.ToUniversalTime() so there is no SQL inside that function and as its an assembly I cannot get access to the actual code, but if I had to guess I’d say it would looks something like this.
using System;
using System.Data;
using Microsoft.SqlServer.Server; using System.Data.SqlTypes;

public class HelloWorldProc
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void MyOwnToUTCFunc(DateTime LocalTime, out DateTime UTC)
{
UTC = LocalTime.ToUniversalTime(); SqlContext.Pipe.Send(UTC.ToString());
}
}

So once you’ve compiled this to an assembly you have to tell SQL where the assembly is and then create a stored procedure that uses the assembly, then you can use it like a normal SQL Stored Procedure.

This link goes into programming your own extended procedures which is how the above would work. http://technet.microsoft.com/en-us/library/ms190790(v=sql.105).aspx (TSQL Side) http://technet.microsoft.com/en-us/library/ms131102(v=sql.105).aspx (Programming Side) http://technet.microsoft.com/en-us/library/ms131052(v=sql.105).aspx (Hello world of CLR procedures)

That might be overkill and if the inbuilt one doesn’t work then maybe one your write yourself wouldn’t work either.

A definition for local time is “The local time is equal to the Coordinated Universal Time (UTC) time plus the UTC offset.”

So maybe you could use that definition to get the time by working out the HourOffset using GetDate() and GetUTCDate() then using that in the DateAdd function to add or subtract hours from the date your interested in depending if you are converting to UTC or to Local?

Below is some SQL I was just playing with, don’t know if that works for your situation?

Declare @Local DateTime
Declare @UTC DateTime
Set @Local = GetDate()
Set @UTC = GETUTCDATE()
Declare @HourOffSet int
Set @HourOffSet = DateDiff(hour, @Local, @UTC)

--Create a test date
Declare @TestDate DateTime
Set @TestDate = '11/10/13 11:20:34'

--Convert a Test date to UTC from Local
--Add the negated time offset to the test date Declare @ConvertToUTC DateTime
Set @ConvertToUTC = DateAdd(hour, -(@HourOffSet), @TestDate)

--Convert a Test date to Local from UTC
--Add the time offset to the test date Declare @ConvertToLocal DateTime
Set @ConvertToLocal = DateAdd(hour, @HourOffSet, @TestDate)
Select @HourOffSet 'Time offset' , @Local 'Local Time Value', @UTC 'UTC Time Value', @TestDate 'Test Date', @ConvertToUTC 'Converted To UTC',
@ConvertToLocal 'Converted to Local'
"