Data type : GMToffset in SQL Server 2000

5 pts.
Tags:
Microsoft SQL Server 2000
how this data type GMToffset will work.?? can you please help me in that where the offset is a feild in the table & GMToffset is its data type exist in sql server 2000 or it is derived ?? why we use GMT instead of local time?? Is this data type x

Answer Wiki

Thanks. We'll let you know when a new response is added.

There is no datatype in SQL Server 2000 which supports UTC Offset. This feature was introduced in SQL Server 2008.

-Mrdenny

—————–

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″

-Kccrosser

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Your question is not clear, please try to reword it. AFAIK there is no GMToffset type in Sql Server. There is a DATETIMEOFFSET type, which is new in Sql Server 2008.
    70,200 pointsBadges:
    report
  • NSH
    [...] Data type : GMToffset in SQL Server 2000, asked by NSH and answered by MrDenny, CarlosDL and [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following