For a long time, datetime data was a bit of an annoyance, forcing database developers to jump through hoops to remove a time or date component when it wasn’t needed. Although SQL Server 2008 corrected that shortcoming by introducing pure date and pure time data types, there are still a few tasks that are difficult to perform using T-SQL. Common examples include converting string representations of a date and time to the SQL-recognized datetime data type, converting to and from Coordinated Universal Time (UTC), and dealing with Daylight Saving Time (DST) and leap years.
To make these tasks easier, I created a CLR class named DateCLR. It contains five methods.
ConvertStringToDate. T-SQL’s CONVERT function is typically used to convert various date and time formats to the datetime data type. This function includes a number of date and time styles. However, if your data doesn’t exactly match any of the styles, the conversion will fail. For example, the conversion
SELECT CONVERT(datetime, 'Dec 31 2010 12:34:56 A.M.', 120)
fails because of the periods in a.m. The conversion
SELECT CONVERT(datetime,'2010-07-06T12:34:56.789+12:00', 127)
also fails because of the time zone. In both cases, you need to manually perform string manipulations to successfully convert the data to the datetime data type.
Rather than manually perform string manipulations, I created the ConvertStringToDate method to convert string representations of a date and time to their datetime equivalent. This method uses the TryParse method of the DateTime structure in the .NET Framework's System namespace.
ConvertUtcToLocal and ConvertLocalToUtc. Another task that’s difficult to accomplish in T-SQL is converting local time to UTC time and vice versa. Although you can use T-SQL’s GETUTCDATE function to get the system UTC date and time and calculate the UTC offset to use in your conversion, this will work only for current dates. If you have historical dates, the results can be incorrect due to DST. To convert historical dates, I created the ConvertUtcToLocal and ConvertLocalToUtc methods. They use two DST-aware methods: the ToUniversalTime and ToLocalTime methods of the DateTime structure. There’s one caveat, though: ToUniversalTime and ToLocalTime apply a country’s current DST rules to all historical dates, even the dates before the DST was introduced or changed. So, before you use ConvertUtcToLocal or ConvertLocalToUtc, you should find when the latest DST law change occurred in the target country by searching the Internet, then make sure you aren’t using ConvertUtcToLocal or ConvertLocalToUtc on dates prior to that.
IsDaylightSaving and IsLeapYear. To determine whether a date is in a DST period or whether a year is a leap year in T-SQL, you have to maintain a table of entries. The .NET Framework has two methods that can do this for you: the IsDaylightSavingTime and IsLeapYear methods of the DateTime structure. Like ToUniversalTime and ToLocalTime, IsDaylightSavingTime applies a country’s current DST rules to all historical dates. So, the IsDaylightSaving method will work correctly only for inputs dating back to the most recent DST law change.
The full code—including the Visual Studio project files and the compiled DLL—for the DateCLR class is available for download. Go to the top of this page and click the "Download the Code" link.
Deploying DateCLR is similar to deploying FileCLR. One difference is that the database doesn’t need to have the TRUSTWORTHY option enabled. The file at the top of the page includes the CreateDateClrAssembly&Procs.sql script, which you can use to create the assembly that deploys the DateCLR DLL and create the CLR functions that point to the methods. The "Download the Code" file also includes ExploreDateClr.sql, which has examples of how to use the DateCLR class methods.
