SQL Server 2012

Key: Eastern Standard Time

Offset: -240

.Net 3.5 TimeZoneInfo

Key: Eastern Standard Time

Offset: -300

Hi,

This should be correct because 

SYSDATETIMEOFFSET

 gives you the offset from GMT whereas TimeZoneInfo does not .

My understanding is that the timezoneinfo.baseutcoffset gives the offset from UTC or GMT so they should both be the same.

How do you get the value / how does your code look like?

By the difference of 1 hour I guess: Daylight Saving? Do you query the time offset both with the same date?

her are some code snippets

SQL Code

DECLARE @TimeZone VARCHAR(50), @TimeZoneOffset as datetimeoffset
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation', 'TimeZoneKeyName', @TimeZone OUT
set @TimeZoneOffset = SYSDATETIMEOFFSET()

SELECT @TimeZone as TimeZone, datepart(TZOFFSET, @TimeZoneOffset) as [tzOffset]

.Net 3.5 Code

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
    Dim tzCollection As ReadOnlyCollection(Of TimeZoneInfo)
    tzCollection = TimeZoneInfo.GetSystemTimeZones()

    'Index: 92
    'Key: Eastern Standard Time 
    'Offset: -300

    For index As Integer = 0 To tzCollection.Count - 1
        Debug.Print("Index: " & index.ToString & vbTab & "Key: " & tzCollection(index).Id & vbTab & vbTab & "Offset: " & tzCollection(index).BaseUtcOffset.TotalMinutes)
    Next
End Sub

SYSDATETIMEOFFSET() returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.

GetSystemTimeZones returns a sorted collection of all the time zones about which information is available on the local system.

Are you testing the .net code on the same machine of SQL Server?

SYSDATETIMEOFFSET() returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.

GetSystemTimeZones returns a sorted collection of all the time zones about which information is available on the local system.

Are you testing the .net code on the same machine of SQL Server?

• Proposed as answer by Tom PhillipsModerator 18 hours 31 minutes ago

Not on the same computer but on the same domain.

The 2 machines you are testing have different server timezone settings.  This is causing the difference.

The answer to the question was a combination of a couple of the postings.  The simple answer is that SQL Server's sysdatetimeoffset() function returns the current offset with the adjustment rule already applied where as .Net's timezoneinfo.baseutcoffset does not. In .Net you have to apply the appropriate adjustment rule to get the current offset.

• Marked as answer by jwbutler123 11 hours 40 minutes ago
• Edited by jwbutler123 11 hours 37 minutes ago to make it more clear