There is no datatype in SQL Server 2000 which supports UTC Offset. This feature was introduced in SQL Server 2008.
There is no “GMTOffset” native data type in SQL Server. However, <b>in SQL Server 2008</b> you can achieve such a data type using the DATETIMEOFFSET data type, which stores DATETIME data with a user-definable “offset” from a reference time zone.
“<b>Why use GMT instead of local time?</b>”
If all of your system transactions occur “locally”, within the same time zone, AND you don’t have any issues with changes between Standard and Daylight Savings Time, then there is no reason to consider using a global reference time such as GMT (or more correctly, Universal Coordinated Time – UCT).
For a lot of systems, the local DATETIME value is sufficient. For any system where the actual time is very important, and things like DST offsets are critical, then most systems will use a UCT time base and track local times a UCT time with a local offset. The local offset then changes by one hour (+ or -) when changing from DST to Standard Time.
This requires that your system rigidly enforce the data recording and offset conversions, and track the changes in the offset when DST changes. Also, if you have users located in different time zones, you may need to consider the “local” time of the user as well as the “local” time of server.
If you always use the DATETIME value, consider what happens when DST shifts. When the time clock goes from 02:59:59am back to 02:00:00am, will your system record that two successive events actually happened in reverse order? When the time clock goes from 01:59:59am to 03:00:00am, will your system seem to show two events that were really minutes (or seconds) apart appear to have taken over an hour?
If all time events are recorded with the UCT time that they occurred (which doesn’t shift with DST) and the offset to the local time zone where they occurred, you can accurately reconstruct the true sequence of events and true durations.
If I record a datetime value of “2010-04-27 10:59:37.4029112″, is that 10:59am in California, in Florida, in Lahore, in London, in Paris?
If I record a datetimeoffset value of “2010-04-27 19:59:37.4029112 +09:00″, then we know that it happened at UCT time “2010-04-27 19:59:37.4029112 ” in the time zone that is 9 hours West of Greenwich (i.e., the U.S. Pacific Time Zone).
If I then want the value displayed in the local time, just convert it back to a datetime value using the “127″ style (ISO 8601 with time zone).
declare @dateLocal datetime
declare @dateUCT datetimeoffset
declare @dateLocal2 datetime
set @dateLocal = SysDateTime()
@dateLocal = “2010-04-27 10:59:37.4029112″
set @dateUCT = SWITCHOFFSET(CONVERT(datetimeoffset, sysdatetime()),’+09:00′)
@dateUCT = “2010-04-27 19:59:37.4029112 +09:00″
set @dateLocal2 = Convert(datetime, @dateUCT, 127)
@dateLocal2 = “2010-04-27 10:59:37.4029112″