Download Code here

I come across a little issue when migrating Appointment dates from a legacy system to Microsoft CRM Dynamics, we found that the date time that was being migrated was being adjusted based on the clients time zone, assuming that the time zone was GMT Standard time.

This is how I solved the problem, it might not be the best solution but it gave me some hands on time developing which was great after months of pure TSQL scripting and watching data move from one system to another, I used the excuse to get my hands dirty and write some code even if it was just 4 lines of code.

Below is the a snippet of code I copied from one of the CRM views, it takes the Scheduled Start Date and recalculates the dates assuming it is GMT Standard time and adding the hours based on the client time zone.

 

dbo.fn_UTCToTzSpecificLocalTime([ServiceAppointment].[ScheduledStart],
us.TimeZoneBias,
us.TimeZoneDaylightBias,
us.TimeZoneDaylightYear,
us.TimeZoneDaylightMonth,
us.TimeZoneDaylightDay,
us.TimeZoneDaylightHour,
us.TimeZoneDaylightMinute,
us.TimeZoneDaylightSecond,
0,
us.TimeZoneDaylightDayOfWeek,
us.TimeZoneStandardBias,
us.TimeZoneStandardYear,
us.TimeZoneStandardMonth,
us.TimeZoneStandardDay,
us.TimeZoneStandardHour,
us.TimeZoneStandardMinute,
us.TimeZoneStandardSecond,
0,
us.TimeZoneStandardDayOfWeek)

I wrote this little User defined function CLR; it has two parameters: one _datetime and _timezone. It’s pretty simple; add the datetime for the timezone.

//———————————————————————
// <copyright file=”BICGFunctions.cs” company=”bicg”>
//      Copyright (c) bicg.  All rights reserved.
// </copyright>
// <summary>
// Converts any Datetime to GMT Standard from the specified time zone
// </summary>
// <author>
//      Cody Middlebrook
// </author>
//———————————————————————using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions
{
///<summary>
/// Function Converts the Date Time from a Specified Time Zone to GMT Standard Time
///</summary>
///<param name=”_datetime”>Date Time from the Time Zone Specified</param>
///<param name=”_timezone”>Needs to be a vaild Time Zone name.</param>
///<returns></returns>[Microsoft.SqlServer.Server.SqlFunction]
public static System.Data.SqlTypes.SqlDateTime ConvertToGMT(System.Data.SqlTypes.SqlDateTime _datetime,System.Data.SqlTypes.SqlString _timezone)
{
//Set the Time zone info to the from time zone passed into the function
TimeZoneInfo convetFromZone = TimeZoneInfo.FindSystemTimeZoneById((string)_timezone);
//Set the GMT Time zone to always be GMT Standard Time.
TimeZoneInfo GMTZone = TimeZoneInfo.FindSystemTimeZoneById(“GMT Standard Time”);//=======================================================
// Returns the GMT Time from the time zone passed into the function
//============================================================
DateTime ConvertedTime = TimeZoneInfo.ConvertTime((DateTime)_datetime, convetFromZone,GMTZone);
return ConvertedTime;
}
}

To deploy this function I ran the following in this order

–======================================================
–The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts
–the database and the contents within it. By default, this setting is OFF, but can be set to ON
–by using the ALTER DATABASE statement. For example, ALTER DATABASE AdventureWorks2012 SET TRUSTWORTHY ON;.
–======================================================ALTER DATABASE FOMStage SET TRUSTWORTHY ON

 

–===========================================================
–The common language runtime (CLR) integration feature is off by default, and must be enabled
–in order to use objects that are implemented using CLR integration. To enable CLR integration,
–use the clr enabled option of the sp_configure stored procedure:
–==========================================================
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘clr enabled’, 1;
GO
RECONFIGURE;
GO

 

–==================================================
–Creates a managed application module that contains class metadata and managed code as an object
–in an instance of SQL Server. By referencing this module, common language runtime (CLR) functions,
–stored procedures, triggers, user-defined aggregates, and user-defined types can be created in the database.
–====================================================CREATE ASSEMBLY [BICG.TimeZone.To.GMT]
AUTHORIZATION [dbo]
FROM ‘C:\BICG.TimeZone.To.GMT.dll’
WITH PERMISSION_SET = UNSAFE

Create the user defined function

CREATE FUNCTION [dbo].[ConvertToGMT] (
@_datetime DATETIME
,@_timezone NVARCHAR(MAX)
)
RETURNS DATETIME
AS
EXTERNAL NAME [BICG.TimeZone.To.GMT].[UserDefinedFunctions].[ConvertToGMT]

Here is the example and the results from running the function passing in a date and time from Western Australia

SELECT [dbo].[ConvertToGMT](’12 Dec 2014′,’W. Australia Standard Time’) as GMTStandard

ConvertToGMT

 

List of Time zones

Afghanistan Standard Time
Alaskan Standard Time
Alaskan Standard Time\Dynamic DST
Arab Standard Time
Arabian Standard Time
Arabic Standard Time
Arabic Standard Time\Dynamic DST
Argentina Standard Time
Argentina Standard Time\Dynamic DST
Atlantic Standard Time
Atlantic Standard Time\Dynamic DST
AUS Central Standard Time
AUS Eastern Standard Time
AUS Eastern Standard Time\Dynamic DST
Azerbaijan Standard Time
Azores Standard Time
Azores Standard Time\Dynamic DST
Bahia Standard Time
Bahia Standard Time\Dynamic DST
Bangladesh Standard Time
Bangladesh Standard Time\Dynamic DST
Canada Central Standard Time
Cape Verde Standard Time
Caucasus Standard Time
Caucasus Standard Time\Dynamic DST
Cen. Australia Standard Time
Cen. Australia Standard Time\Dynamic DST
Central America Standard Time
Central Asia Standard Time
Central Brazilian Standard Time
Central Brazilian Standard Time\Dynamic DST
Central Europe Standard Time
Central European Standard Time
Central Pacific Standard Time
Central Standard Time
Central Standard Time\Dynamic DST
Central Standard Time (Mexico)
China Standard Time
Dateline Standard Time
E. Africa Standard Time
E. Australia Standard Time
E. Europe Standard Time
E. South America Standard Time
E. South America Standard Time\Dynamic DST
Eastern Standard Time
Eastern Standard Time\Dynamic DST
Egypt Standard Time
Egypt Standard Time\Dynamic DST
Ekaterinburg Standard Time
Ekaterinburg Standard Time\Dynamic DST
Fiji Standard Time
Fiji Standard Time\Dynamic DST
FLE Standard Time
Georgian Standard Time
GMT Standard Time
Greenland Standard Time
Greenland Standard Time\Dynamic DST
Greenwich Standard Time
GTB Standard Time
Hawaiian Standard Time
India Standard Time
Iran Standard Time
Iran Standard Time\Dynamic DST
Israel Standard Time
Israel Standard Time\Dynamic DST
Jordan Standard Time
Jordan Standard Time\Dynamic DST
Kaliningrad Standard Time
Kaliningrad Standard Time\Dynamic DST
Kamchatka Standard Time
Korea Standard Time
Libya Standard Time
Libya Standard Time\Dynamic DST
Magadan Standard Time
Magadan Standard Time\Dynamic DST
Mauritius Standard Time
Mauritius Standard Time\Dynamic DST
Mid-Atlantic Standard Time
Middle East Standard Time
Middle East Standard Time\Dynamic DST
Montevideo Standard Time
Montevideo Standard Time\Dynamic DST
Morocco Standard Time
Morocco Standard Time\Dynamic DST
Mountain Standard Time
Mountain Standard Time\Dynamic DST
Mountain Standard Time (Mexico)
Myanmar Standard Time
N. Central Asia Standard Time
N. Central Asia Standard Time\Dynamic DST
Namibia Standard Time
Namibia Standard Time\Dynamic DST
Nepal Standard Time
New Zealand Standard Time
New Zealand Standard Time\Dynamic DST
Newfoundland Standard Time
Newfoundland Standard Time\Dynamic DST
North Asia East Standard Time
North Asia East Standard Time\Dynamic DST
North Asia Standard Time
North Asia Standard Time\Dynamic DST
Pacific SA Standard Time
Pacific SA Standard Time\Dynamic DST
Pacific Standard Time
Pacific Standard Time\Dynamic DST
Pacific Standard Time (Mexico)
Pakistan Standard Time
Pakistan Standard Time\Dynamic DST
Paraguay Standard Time
Paraguay Standard Time\Dynamic DST
Romance Standard Time
Russian Standard Time
Russian Standard Time\Dynamic DST
SA Eastern Standard Time
SA Pacific Standard Time
SA Western Standard Time
Samoa Standard Time
Samoa Standard Time\Dynamic DST
SE Asia Standard Time
Singapore Standard Time
South Africa Standard Time
Sri Lanka Standard Time
Syria Standard Time
Syria Standard Time\Dynamic DST
Taipei Standard Time
Tasmania Standard Time
Tasmania Standard Time\Dynamic DST
Tokyo Standard Time
Tonga Standard Time
Turkey Standard Time
Turkey Standard Time\Dynamic DST
Ulaanbaatar Standard Time
US Eastern Standard Time
US Eastern Standard Time\Dynamic DST
US Mountain Standard Time
UTC
UTC+12
UTC-02
UTC-11
Venezuela Standard Time
Venezuela Standard Time\Dynamic DST
Vladivostok Standard Time
Vladivostok Standard Time\Dynamic DST
W. Australia Standard Time
W. Australia Standard Time\Dynamic DST
W. Central Africa Standard Time
W. Europe Standard Time
West Asia Standard Time
West Pacific Standard Time
Yakutsk Standard Time
Yakutsk Standard Time\Dynamic DST

Leave a Comment

Your email address will not be published. Required fields are marked *