Solve Time Zone, GMT, and UTC problems using the T-SQL Toolbox database

Introduction

Soon enough, the applications and database software we build will handle date and time values. The T-SQL Toolbox database – a free download – can help solve complex calculations with those values.

I live in the U.S., as do many of my customers. We must deal with daylight savings time, which means that on specific dates, the local time in a specific time zone can jump forward one hour, and on other dates, the local time can fall back one hour. These changes happen on a defined schedule. A forward change always follows a backward change, and a backward change always follows a forward change. They never accumulate. Dealing with all this in the database layer can become really complex, and T-SQL Toolbox can save a lot of effort with this. At a conference, I heard a speaker explain that we should use Greenwich Mean Time, or GMT, for database date and time values to avoid this issue. GMT never changes for daylight savings time, and we can easily calculate local time values based on GMT data. Although SQL Server offers plenty of built-in date and time functions, those functions don’t directly handle conversions between local time zone values and GMT, and they certainly don’t handle daylight savings time conversions. Fortunately, I found the T-SQL Toolbox as a way to avoid building the functions that I would need for all of this. Available here at the CodePlex Archive, and here at GitLab, the T-SQL Toolbox provides SQL Server user-defined functions (UDFs) that convert date/time values between time zones, including GMT. T-SQL Toolbox also provides UDFs that calculate starting or ending

day
week
month
quarter
year

DateTime values for a given date/time value.

Download and Install

After download:

and file extraction, as shown below:

As seen in the following screenshot, drill down into the sourceCode directory:

Then, drill down into the sourceCode.zip file. Copy the TSqlToolbox.sql file

We’ll run the TSqlToolbox.sql file to install the T-SQL Toolbox database in SQL Server. I installed it in a SQL Server 2014 Standard Edition environment on a Windows 10 PC.

The TSqlToolbox.sql file has a CREATE DATABASE script, and sets of CREATE TABLE and CREATE FUNCTION scripts. Open TSqlToolbox.sql in a SQL Server query window

This will create the complete TSqlToolbox database, its tables, table data rows, functions, and primary/foreign key constraints. In the screenshot above, the FRANK-PC database server hosts the TSqlToolbox. Every database that the FRANK-PC database server hosts can use the TSqlToolbox functions.

Under the Hood

The T-SQL Toolbox database uses data in the following tables for its calculations.

DateTimeUtil.Timezone
DateTimeUtil.TimezoneAdjustmentRule

The Timezone table has one row for each time zone defined in the database.

CREATE TABLE [DateTimeUtil].[Timezone](

[Id] [int] NOT NULL,

[Identifier] [nvarchar](100) NULL,

[StandardName] [nvarchar](100) NULL,

[DisplayName] [nvarchar](100) NULL,

[DaylightName] [nvarchar](100) NULL,

[SupportsDaylightSavingTime] [bit] NULL,

[BaseUtcOffsetSec] [int] NULL

)

The .Net TimeZoneInfo class can serve as the primary data source for this table because, as seen here at the Microsoft documentation, this class has the following properties:

Id
StandardName
DisplayName
DaylightName
SupportsDaylightSavingTime
BaseUtcOffset

The T-SQL Toolbox TimezoneAdjustmentRule table has rows that show historical metadata changes for each time zone change, the present defining metadata for each time zone, and planned future changes for specific time zones.

CREATE TABLE [DateTimeUtil].[TimezoneAdjustmentRule](

[Id] [int] NOT NULL,

[TimezoneId] [int] NULL,

[RuleNo] [int] NULL,

[DateStart] [datetime2](7) NULL,

[DateEnd] [datetime2](7) NULL,

[DaylightTransitionStartIsFixedDateRule] [bit] NULL,

[DaylightTransitionStartMonth] [int] NULL,

[DaylightTransitionStartDay] [int] NULL,

[DaylightTransitionStartWeek] [int] NULL,

[DaylightTransitionStartDayOfWeek] [int] NULL,

[DaylightTransitionStartTimeOfDay] [time](7) NULL,

[DaylightTransitionEndIsFixedDateRule] [bit] NULL,

[DaylightTransitionEndMonth] [int] NULL,

[DaylightTransitionEndDay] [int] NULL,

[DaylightTransitionEndWeek] [int] NULL,

[DaylightTransitionEndDayOfWeek] [int] NULL,

[DaylightTransitionEndTimeOfDay] [time](7) NULL,

[DaylightDeltaSec] [int] NULL

)

The .Net TimeZoneInfo.AdjustmentRule class can serve as the primary data source for this table because, as seen here at the Microsoft documentation, its properties and methods mirror many of the columns of this table. We can clearly see this when we research the DateEnd and DaylightTransitionStart properties of this class, for example.

Using T-SQL Toolbox

We’ll use a custom database to work with TSqlToolbox. Run this script in a SQL Server query window:

USE [master]

-- Use

-- xp_create_subdir

-- to build a directory for the MDF and LDF

-- files of this database, if necessary.

EXEC master.dbo.xp_create_subdir 'C:\TSQL_TOOLBOX_DEMO'

CREATE DATABASE [TSQL_TOOLBOX_DEMO]

ON PRIMARY

( NAME = N'TSQL_TOOLBOX_DEMO',

FILENAME = N'C:\TSQL_TOOLBOX_DEMO\TSQL_TOOLBOX_DEMO.mdf' ,

SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON

( NAME = N'TSQL_TOOLBOX_DEMO_log',

FILENAME = N'C:\TSQL_TOOLBOX_DEMO\TSQL_TOOLBOX_DEMO_log.ldf' ,

SIZE = 1024KB , MAXSIZE = 1GB , FILEGROWTH = 10%)

USE [TSQL_TOOLBOX_DEMO]

CREATE PROCEDURE [dbo].[CONVERT_GETDATE_VALUE_FROM_ONE_TIMEZONE_TO_UTC]

@timezoneID INT -- an integer mapped to a specific time zone; see the

-- Tsqltoolbox.DateTimeUtil.Timezone table for all

-- time zones

-- To test: CONVERT_GETDATE_VALUE_FROM_ONE_TIMEZONE_TO_UTC 6;

-- parameter value 6 maps to 'Pacific Standard Time' in the

-- TSqlToolbox.DateTimeUtil.Timezone

-- table

DECLARE @localTimeZone as NVARCHAR(50);

SET @localTimeZone = (

SELECT identifier

FROM TSqlToolbox.DateTimeUtil.Timezone

WHERE Id = @timezoneID

);

SELECT @localTimeZone AS 'LOCAL_TIME_ZONE_IDENTIFIER',

GETDATE() AS 'LOCAL_GETDATE()_VALUE',

TSqlToolbox.DateTimeUtil.UDF_ConvertLocalToUtcByTimezoneId

(@timezoneID, GETDATE())

AS 'UTC_GETDATE()_VALUE_EQUIVALENT';

If necessary, it first creates a subdirectory, \TSQL_TOOLBOX_DEMO on the root, and then creates a new database called TSQL_TOOLBOX_DEMO. It places the component LDF and MDF files of the T-SQL Toolbox database in the TSQL_TOOLBOX_DEMO directory. Lastly, it creates the CONVERT_GETDATE_VALUE_FROM_ONE_TIMEZONE_TO_UTC stored procedure, as shown.

In the stored procedure SELECT statement, the third expression on lines 25 and 26 calls the TSqlToolbox DateTimeUtil.UDF_ConvertLocalToUtcByTimezoneId function. Like many other TSqlToolbox functions, this function accounts for daylight savings time through calls to the TSqlToolbox.DateTimeUtil.TimezoneAdjustmentRule table.

1 2	TSqlToolbox.DateTimeUtil.UDF_ConvertLocalToUtcByTimezoneId (@timezoneID, GETDATE())

The call at lines 25 and 26 places the TSqlToolbox database name in front of the complete function name. Then it adds the @timezoneID and GETDATE() parameters. Note that the functions have two-part names. “DateTimeUtil” serves as the prefix of all TSqlToolbox function names, and a dot separates the second identifying function name. If a TSqlToolbox function name did not include the “DateTimeUtil” prefix, we would need to substitute the name of the TSqlToolbox database default schema. We usually use dbo for this; “dbo” stands for “database owner.” However, different database environments might handle this naming differently, so check with a database administrator for exact details.

At line 5, the demo stored procedure has test code for the stored procedure itself. The test code returns this row:

At line 17, it maps the @timezoneID parameter to the time zone name. The Line 23 SELECT statement builds the result set of the finished stored procedure. This shows that with a call to a TSqlToolbox function, we can easily map a local date-time value to a GMT value. In this case, we tried a call to GETDATE(). TSqlToolbox accounts for any local daylight savings time effects.

When we look at the T-SQL Toolbox database functions, we can see that they both lever native SQL Server functions and other TSqlToolbox functions. For example, the UDF_GetStartOfDay function first converts the @ReferenceDate parameter – originally a DATETIME2 data type – to a DATE data type. This strips the time data from the @ReferenceDate value. Then, it converts this intermediate value back to a DATETIME2 data type. This conversion restored the time data but zeroed out the time to Start Of Day (SOD).

The next screenshot shows the equivalent conversions.

As seen below, the TSqlToolbox UDF_ConvertLocalToLocalByTimezoneIdentifier function calls the TSqlToolbox: UDF_ConvertUtcToLocalByTimezoneIdentifier and UDF_ConvertLocalToUtcByTimezoneIdentifier functions to convert a DATETIME2 parameter from one time zone to another. The first two parameters have values from the column [TSqlToolbox].[DateTimeUtil].[Timezone].Identifier.

Extend TSqlToolbox

As explained earlier, the T-SQL Toolbox database offers functions to calculate start or end day, week, etc. DateTime values for a given DATETIME2 value. We can easily extend these functions. For example, I built and added the UDF_GetEndOfDecade function, seen here, to my local TSqlToolbox database. It calculates the end of decade DATETIME2 value for a given DATETIME2 parameter.

CREATE FUNCTION DateTimeUtil.UDF_GetEndOfDecade

(

@ReferenceDate DATETIME2

)

RETURNS DATETIME2

BEGIN

-- GEOY = "Get End Of Year"

DECLARE @GEOY DATETIME2 = NULL

DECLARE @GEOYYear INT

DECLARE @GEOYMonth INT

DECLARE @GEOYDay INT

DECLARE @YearOffset INT

DECLARE @Result DATETIME2 = NULL

-- Samples:

-- SELECT TSqlToolbox.DateTimeUtil.UDF_GetEndOfDecade(GETDATE())

-- SELECT TSqlToolbox.DateTimeUtil.UDF_GetEndOfDecade('3023-05-14 12:45:13')

-- Calculate the EOY value for the input parameter.

SET @GEOY = [TSqlToolbox].[DateTimeUtil].UDF_GetEndOfYear (@ReferenceDate);

-- Parse the EOY value into separate MM / DD / YY values.

SET @GEOYYear = DATEPART(year, @GEOY);

SET @GEOYMonth = DATEPART(month, @GEOY);

SET @GEOYDay = DATEPART(day, @GEOY);

-- The % (mod function) calculates the number of years

-- remaining in the decade. Subtract this value from

-- 10 as the number of years in a decade, and then

-- subtract 1 to end the decade in year 99 as shown

-- in this

-- XX99-12-31 23:59:9999999

-- format.

SET @YearOffset = (10 - (@GEOYYear % 10) - 1)

-- Build a DateTime value with DATEFROMPARTS(), and

-- then call UDF_GetEndOfDay.

SELECT @Result =

[TSqlToolbox].[DateTimeUtil].UDF_GetEndOfDay (DATEFROMPARTS((@GEOYYear + @YearOffset), @GEOYMonth, @GEOYDay));

RETURN @Result;

END

This function first calculates the end-of-year (EOY) value for the input parameter. Then, it parses this EOY value into a separate month, day, and year values. The @YearOffset variable calculation uses the % (mod) function to calculate the number of years remaining in the decade of the parameter value year. Finally, the statement first assembles a finished date value from all of the component values, and then uses the UDF_GetEndOfDay function to convert this value to a DATETIME2 return value.

Update Internal T-SQL Toolbox Data

The TSqlToolbox database functions rely on the data in its Timezone and TimezoneAdjustmentRule tables for their calculations. Obviously, this data does not change in the tables themselves. Matt Johnson‑Pint of Microsoft focuses on Windows time zone engineering. At his blog, he discussed how time zones constantly change. In this Stack Overflow thread, he explained that Windows stores time zone information in the registry. He gave more details about how this works in this Stack Overflow thread. Although the T-SQL Toolbox database has huge value, we need to remember that its core data can become obsolete without updates. The Microsoft Windows update process updates the registry time zone information, so the registry of a fully updated device should have the latest available time zone information. I built this C-Sharp Windows desktop application to query the registry and return the data from the .Net TimeZoneInfo and TimeZoneInfo.AdjustmentRule classes. These classes query the registry time zone values, which we can use to update T-SQL Toolbox. The application uses a pipe symbol “|” to delimit the column values in the rows. I also built t his VB.net version, which works the same way. I hosted the C-Sharp and VB.net versions on my GitHub page. They both include complete Visual Studio 2015 solutions and finished executable files.

To build SQL Server tables with the application output data, first, update Windows on the development machine. This will apply any time zone updates to the registry. Then run the application and copy the rows from the form text boxes. Use Ctrl-C to copy because the form uses rich text boxes. Place the rows in new text files. Truncate the TSqlToolbox database tables you will update so that they will have completely fresh data. Use the SQL Server Import and Export Wizard to import the values into the T-SQL Toolbox database tables. If this wizard fails because of problems with the data, reformat the data for a SQL Server INSERT statement, and proceed with the INSERT statements in a query window. To build the parent/child table relationships, add integer-value ID columns to the tables as primary and foreign keys.

Conclusion

As we saw, T-SQL Toolbox solves tricky problems and returns a huge value. Even better, we can easily extend it and update its data to cover an expanding range of business rules and requirements.

Author
Recent Posts

Frank Solomon

Frank Solomon started out building Microsoft stack products, and he gradually focused on SQL Server. Some years ago, he began a parallel shift to writing and technical writing. He wrote published articles, he blogs at Bit Vectors, and he co-wrote The SQL Workshop for Packt Publishing, with SQL Shack writer Prashanth Jayaram.

See more about Frank at LinkedIn.
Remove the non-standard characters from this address: fb} <s.author@gmail.com to reach him by email.