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

asked 16 Dec '19, 18:05

gchq's gravatar image

accept rate: 27%

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.

(17 Dec '19, 03:42) Volker Barth

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
        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
(17 Dec '19, 11:21) gchq
Replies hidden

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)?

(17 Dec '19, 11:44) Volker Barth

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.

(17 Dec '19, 11:52) gchq
Replies hidden

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...)

(17 Dec '19, 12:08) Volker Barth
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 16 Dec '19, 18:05

question was seen: 627 times

last updated: 17 Dec '19, 12:13