How to keep dates in UTC with MSSQL

  • Normally, Microsoft SQL Server normally keep it's dates in the local time.
  • When dealing with distributed servers, including MSSQL clients from different time zones, you will need to deal with the different time zones.
  • The only way proper way of dealing with multiple time zones is to keep the the datetime data in a UTC [coordinated universal time], aka GMT [Greenwich Mean Time].
  • The problem is that Microsoft SQL Server has no way of knowing what time zone the client is in! SQL Server only knows it's own time zone.
  • The application will have to provide the UTC data for the client information.
  • Most people think of time zone in hourly increments. It only works for most people, but there are many countries where it does not work. Countries like Canada, Australia,... have 1/2 hour time zones. Countries like Nepal are +5:45hr UTC. So you will need to keep track of the time offset in minutes.
  • Microsoft SQL Server 2008, finally, has a date only data type.
  • Microsoft SQL Server 2008, finally, has a datetime with time offset data type. But this will not help the people converting from MSSQL 2000 or MSSQL 2005 to MSSQL 2008, because of the carry over from older versions.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Data used

  1. USE sql911;
  2. SELECT sales_date,
  3.        sales_qty
  4. FROM sales;
sales_date              sales_qty
----------------------- ---------------------------------------
2005-12-22 10:01:01.000 10.00
2006-10-06 10:01:01.000 10.00
2005-11-30 10:01:01.000 0.00
2006-10-06 10:01:01.000 10.00
2005-12-06 11:01:01.000 8.00
2006-02-28 13:01:01.000 0.00
2006-03-19 14:01:01.000 5.00
2006-10-06 10:01:01.000 10.00
2006-10-06 10:01:01.000 10.00
2006-04-20 15:01:01.000 4.00
2006-03-01 16:01:01.000 3.00
2004-10-06 10:01:01.000 0.00
2005-11-21 10:01:01.000 9.00
2006-03-31 17:01:01.000 0.00
2006-01-22 12:01:01.000 7.00
2006-02-23 13:01:01.000 6.00
2005-03-25 14:01:01.000 5.00
2006-10-31 10:01:01.000 10.00
2006-10-29 10:01:01.000 3.00
2007-06-17 17:01:01.000 2.00
2007-04-18 00:00:00.000 0.00
2007-05-31 00:00:00.000 5.00
2007-07-06 00:00:00.000 1.00
2007-05-03 09:00:00.000 3.00
2007-05-05 04:00:00.000 1.00
2006-10-06 10:01:01.000 10.00
2004-03-06 14:01:01.000 0.00
2005-04-06 15:01:01.000 4.00
2005-05-06 16:01:01.000 3.00
2006-06-06 17:01:01.000 2.00
2006-07-06 19:01:01.000 1.00
2006-03-06 14:01:01.000 5.00
2006-04-06 15:01:01.000 4.00
2007-05-07 11:00:00.000 2.00
2006-06-06 17:01:01.000 0.00
2006-07-06 19:01:01.000 1.00
2005-10-06 10:01:01.000 10.00
2007-05-08 15:00:00.000 1.50
2005-12-06 11:01:01.000 8.00
2006-01-06 12:01:01.000 7.00
2007-05-07 11:30:00.000 2.00
2007-05-08 13:30:00.000 1.00
2006-07-06 19:01:01.000 1.00
2007-05-09 14:00:00.000 1.25
2007-11-06 10:01:01.000 9.00
2007-05-10 14:00:00.000 5.00
2007-05-11 14:15:00.000 0.00
2007-05-12 15:00:00.000 0.00
2007-05-13 16:00:00.000 1.00
2007-05-08 05:00:00.000 1.00
2007-05-07 00:00:00.000 2.00
2007-05-14 11:00:00.000 3.00
2006-07-06 00:00:00.000 1.00
2007-05-11 13:00:00.000 3.50
2007-05-08 05:00:00.000 0.00
2007-05-08 07:00:00.000 2.00
2007-05-08 11:00:00.000 3.00

(57 row(s) affected)

UTC calculations

  1. SELECT GETUTCDATE() AS 'Here is the GMT Time',
  2.        CURRENT_TIMESTAMP AS 'Local date & time',
  3.        DATEDIFF(HOUR,CURRENT_TIMESTAMP,GETUTCDATE()) AS 'UTC offset in hours',
  4.        DATEDIFF(MINUTE,CURRENT_TIMESTAMP,GETUTCDATE()) AS 'UTC offset in minutes';
  5. go
Here is the GMT Time    Local date & time       UTC offset in hours UTC offset in minutes
----------------------- ----------------------- ------------------- ---------------------
2007-10-15 16:46:06.707 2007-10-15 09:46:06.707 7                   420

(1 row(s) affected)
  • You should keep track of the offset in minutes because of countries like Canada, Australia,... that have 1/2 hour time zones and countries like Nepal that are +5:45hr UTC. So you will need to keep track of the time offset in minutes.
  • getutcdate() gives the current_timestamp in the UTC format.
  • I am UTC+7 or 7 hours ahead of UTC.

UTC dates

  1. SELECT sales_date,
  2.        DATEADD(MINUTE,DATEDIFF(MINUTE,CURRENT_TIMESTAMP,GETUTCDATE()),sales_date) AS 'UTC sales date',
  3.        sales_qty
  4. FROM sales;
  5. go
sales_date              UTC sales date          sales_qty
----------------------- ----------------------- ---------------------------------------
2005-12-22 10:01:01.000 2005-12-22 17:01:01.000 10.00
2006-10-06 10:01:01.000 2006-10-06 17:01:01.000 10.00
2005-11-30 10:01:01.000 2005-11-30 17:01:01.000 0.00
2006-10-06 10:01:01.000 2006-10-06 17:01:01.000 10.00
2005-12-06 11:01:01.000 2005-12-06 18:01:01.000 8.00
2006-02-28 13:01:01.000 2006-02-28 20:01:01.000 0.00
2006-03-19 14:01:01.000 2006-03-19 21:01:01.000 5.00
2006-10-06 10:01:01.000 2006-10-06 17:01:01.000 10.00
2006-10-06 10:01:01.000 2006-10-06 17:01:01.000 10.00
2006-04-20 15:01:01.000 2006-04-20 22:01:01.000 4.00
2006-03-01 16:01:01.000 2006-03-01 23:01:01.000 3.00
2004-10-06 10:01:01.000 2004-10-06 17:01:01.000 0.00
2005-11-21 10:01:01.000 2005-11-21 17:01:01.000 9.00
2006-03-31 17:01:01.000 2006-04-01 00:01:01.000 0.00
2006-01-22 12:01:01.000 2006-01-22 19:01:01.000 7.00
2006-02-23 13:01:01.000 2006-02-23 20:01:01.000 6.00
2005-03-25 14:01:01.000 2005-03-25 21:01:01.000 5.00
2006-10-31 10:01:01.000 2006-10-31 17:01:01.000 10.00
2006-10-29 10:01:01.000 2006-10-29 17:01:01.000 3.00
2007-06-17 17:01:01.000 2007-06-18 00:01:01.000 2.00
2007-04-18 00:00:00.000 2007-04-18 07:00:00.000 0.00
2007-05-31 00:00:00.000 2007-05-31 07:00:00.000 5.00
2007-07-06 00:00:00.000 2007-07-06 07:00:00.000 1.00
2007-05-03 09:00:00.000 2007-05-03 16:00:00.000 3.00
2007-05-05 04:00:00.000 2007-05-05 11:00:00.000 1.00
2006-10-06 10:01:01.000 2006-10-06 17:01:01.000 10.00
2004-03-06 14:01:01.000 2004-03-06 21:01:01.000 0.00
2005-04-06 15:01:01.000 2005-04-06 22:01:01.000 4.00
2005-05-06 16:01:01.000 2005-05-06 23:01:01.000 3.00
2006-06-06 17:01:01.000 2006-06-07 00:01:01.000 2.00
2006-07-06 19:01:01.000 2006-07-07 02:01:01.000 1.00
2006-03-06 14:01:01.000 2006-03-06 21:01:01.000 5.00
2006-04-06 15:01:01.000 2006-04-06 22:01:01.000 4.00
2007-05-07 11:00:00.000 2007-05-07 18:00:00.000 2.00
2006-06-06 17:01:01.000 2006-06-07 00:01:01.000 0.00
2006-07-06 19:01:01.000 2006-07-07 02:01:01.000 1.00
2005-10-06 10:01:01.000 2005-10-06 17:01:01.000 10.00
2007-05-08 15:00:00.000 2007-05-08 22:00:00.000 1.50
2005-12-06 11:01:01.000 2005-12-06 18:01:01.000 8.00
2006-01-06 12:01:01.000 2006-01-06 19:01:01.000 7.00
2007-05-07 11:30:00.000 2007-05-07 18:30:00.000 2.00
2007-05-08 13:30:00.000 2007-05-08 20:30:00.000 1.00
2006-07-06 19:01:01.000 2006-07-07 02:01:01.000 1.00
2007-05-09 14:00:00.000 2007-05-09 21:00:00.000 1.25
2007-11-06 10:01:01.000 2007-11-06 17:01:01.000 9.00
2007-05-10 14:00:00.000 2007-05-10 21:00:00.000 5.00
2007-05-11 14:15:00.000 2007-05-11 21:15:00.000 0.00
2007-05-12 15:00:00.000 2007-05-12 22:00:00.000 0.00
2007-05-13 16:00:00.000 2007-05-13 23:00:00.000 1.00
2007-05-08 05:00:00.000 2007-05-08 12:00:00.000 1.00
2007-05-07 00:00:00.000 2007-05-07 07:00:00.000 2.00
2007-05-14 11:00:00.000 2007-05-14 18:00:00.000 3.00
2006-07-06 00:00:00.000 2006-07-06 07:00:00.000 1.00
2007-05-11 13:00:00.000 2007-05-11 20:00:00.000 3.50
2007-05-08 05:00:00.000 2007-05-08 12:00:00.000 0.00
2007-05-08 07:00:00.000 2007-05-08 14:00:00.000 2.00
2007-05-08 11:00:00.000 2007-05-08 18:00:00.000 3.00

(57 row(s) affected)
  • Since I am 7 hours from the GMT/UTC, we can see from these entries: 
sales_date              UTC sales date         
----------------------- -----------------------
2005-12-22 10:01:01.000 2005-12-22 17:01:01.000 10.00
2006-03-31 17:01:01.000 2006-04-01 00:01:01.000 0.00
2007-04-18 00:00:00.000 2007-04-18 07:00:00.000 0.00

that the UTC date and time are 7 hours ahead. I am UTC+7 hours or UTC+420 minutes.