Time Zones - SQL Central

[former_SQLA_member1694879]

I have just spotted this in SQL Central (after the latest Windows updates SQL Central is no longer being displayed with text about six inches high, and I can actually see everything)

(Tried to upload image but it keeps throwing an error) - it's at the very bottom of Tables - Views - Indexes...

What does it do? Can't find any documentation on it.

Time Zones have always been a major pain in the rump - MS very helpfully takes a Date (not DateTime), gives it a time of 00:00 hrs then converts to local time so a date of, say, 16 Dec is shown as 15 Dec - we have to convert it back at the application level - which for some reason doesn't work in summer for states like Arizona that do not have daylight savings

[VolkerBarth]

You are relating to the "Time Zones" system folder entry in the Database View?

AFAIK that's to define a simulated time zone and is a new v17 facility to make a database server appear to run in a different time zone than the machine it is running on is actually located in.

[former_SQLA_member1694879]

That's the one - currently we have to change everything to the local time zone using this

Public Function LocalDateFormat(ByVal InputDate As Date) As String

    Dim vDate As String = InputDate.ToString("d", System.Globalization.CultureInfo.InvariantCulture)
    Dim LocalZone As TimeZone = TimeZone.CurrentTimeZone
    Dim CurrentOffset As TimeSpan = LocalZone.GetUtcOffset(InputDate)
    Dim DayLightSaving As Boolean = LocalZone.IsDaylightSavingTime(InputDate)
    Dim CalculatedOffset As New DateTime(InputDate.Ticks, DateTimeKind.Local)
    If CurrentOffset.CompareTo(TimeSpan.Zero) < 0 Then
        CalculatedOffset -= LocalZone.GetUtcOffset(InputDate)
        If DayLightSaving = True Then
            CalculatedOffset = CalculatedOffset.AddHours(1)
        End If
    Else
        CalculatedOffset += LocalZone.GetUtcOffset(InputDate)
        If DayLightSaving = True Then
            CalculatedOffset = CalculatedOffset.AddHours(-1)
        End If
    End If

    InputDate = CalculatedOffset

    Dim vCulture As String = System.Globalization.CultureInfo.CurrentCulture.ToString
    Dim vReturnDate As String = ""
    Select Case vCulture
        Case "en-US"
            vReturnDate = Format(InputDate, "MM/dd/yyyy")
        Case "en-GB"
            vReturnDate = Format(InputDate, "dd/MM/yyyy")
        Case Else
            vReturnDate = Format(InputDate, "dd/MM/yyyy")
    End Select
    Return vReturnDate

End Function

[VolkerBarth]

Just to understand:

• Do you have a database running in timezone A and want to appear it as running in timezone B because all users of that database expect it to run there? (That's what the "Simulated time zone" support is meant for AFAIK.)

• Or do you have a database with users located in different time zones and your users (or some of them) face time zone-related issues within your applications (so this is basically a client-side issue)?

[former_SQLA_member1694879]

It's been an ongoing client-side issue forever. According to MS a returned date field should be just that and not try to convert it from UTC (server time) to the local time zone, but that is just exactly what it does - it can be run in debug and shows the date being manipulated from date 00:00 to, as an example, day before date 16:00.

[VolkerBarth]

Can't comment on that but when it is a client-side issue for some of your clients, then I guess the simulated time zone feature does not solve that, as that would affect all users...

(In other words, I guess this should be solved via UTC or the TimeZoneAdjustment connection property...)