MartyConnelly
martyconnelly at shaw.ca
Wed Aug 9 15:28:10 CDT 2006
Well if your server is running on UTC, it never undergoes a Daylight Savings
or Standard time change. There is a difference between GMT and UTC
Now you get to make an executive decision
do you base all your reports on local time or UTCDateTime.
If local then on queries add the time difference from parameters
and on reports subtract time difference to dates on returned views or
tables etc.
Using DateAdd or DateDiff in your SQL.
Not all time zones are 1 hour interval differences, Newfoundland has a
half hour offset.
You can get both times from SQL Server 2000 via this
Function GetServerUTCDate()
Dim dteServerDateUTC As Date
Dim dteServerDateLocal As Date
'assumes connection to server
'GETUTCDATE works on SQL 2000 +
dteServerDateUTC = CurrentProject.Connection.Execute("SELECT
GETUTCDATE()").Collect(0)
dteServerDateLocal = CurrentProject.Connection.Execute("SELECT
GETDATE()").Collect(0)
Debug.Print "Local Time " & dteServerDateLocal
Debug.Print "UTC Time " & dteServerDateUTC
'delta hour server difference
Debug.Print "Difference in hours " & DateDiff("h", dteServerDateUTC,
dteServerDateLocal)
'or local machine time
Debug.Print "Difference in hours " & DateDiff("h", dteServerDateUTC, Now)
'This delta time difference will change locally twice a year at 2AM
October, April US
End Function
John Skolits wrote:
>Marty,
>
>We are in the EST and I believe the company is in Sweden.
>
>Thanks for the possible solution below. I can certainly use it for my static
>reports.
>
>But my other issues is many people reference these tables through Access.
>Therefore if they do any custom queries on their own, they will always be 4
>hours off.
>
>One solution is to make SQLServer views of each table that they use. I can
>place the date/time conversions in the views. But I'm thinking that will
>cause query performance issues. Usually, I have found views to work well
>with criteria specified on a date/time field without a heavy performance
>hit. But I wonder if I have a date/time formula conversions in the view, if
>the indexes won't work well.
>
>Any thoughts?
>
>John
>
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
>MartyConnelly
>Sent: Tuesday, August 08, 2006 6:44 PM
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer] UTC Date Time Fields and Baan with SqlServer
>
>This line "They used to use standard time fields but now using UTC"
>makes me think
>that they are accepting data from multiple time zones and have
>standardized the time of their SQL
>Server on UTC Greenwich time. This way all their data input times are
>the same, no matter
>what time zone it is coming from. This quite common in large organizations.
>
>The 4 hour time difference is correct as UTC is one hour
>a head of British Standard time and I'll bet you are running in
>American EST timezone.
>What you want to do is query your client machine for its local time and
>subtract or add the difference
>from UTC. So you will need code below. The date of a time zone DST
>change is in an odd format in the registrty.
>
>Have a look at the code in this basClock module Access97 mdb
>It gets local machine time and UTC.
>It handles a lot of the little glitches like daylight saving time switches
>for example Europe switches DST a week before the US.
>UTC time doesn't use DST and some US states don't either.
>Windows local time handles most of these but not in weird
>cases like Sydney Australia changing their time zone for
>their Olympics for a month.
>
>http://www.mvps.org/access/forms/frm0051.htm
>
>
>
>askolits at ot.com wrote:
>
>
>
>>I'm working for a customer who has an MRP system called BAAN. All data
>>tables are on SqlServer.
>>
>>
>>
>>For some reason, when we look at the data through SqlServer. All the times
>>are 4 hours off. When we use their app, the time is correct.
>>
>>The question is, is it their app or a setting we need to set in SqlServer.
>>The claim it's not there issue and they don't support direct access to the
>>data through SQL Server.
>>
>>
>>
>>We have written custom reports to get the many reports their system doesn't
>>provide.
>>
>>
>>
>>Any way to change SQLServer's time so we can fix this issue.
>>
>>Setting the Server's Region codes won't help us since the server is used
>>
>>
>for
>
>
>>other things.
>>
>>I'm thinking there some type of parameter setting we can use.
>>
>>
>>
>>Note: They used to use standard time fields but now using UTC (makes
>>
>>
>sense?)
>
>
>>
>>Thanks,
>>
>>
>>
>>John Skolits
>>
>>
>>
>>
>>
>>_______________________________________________
>>dba-SQLServer mailing list
>>dba-SQLServer at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>>http://www.databaseadvisors.com
>>
>>
>>
>>
>>
>>
>>
>
>
>
--
Marty Connelly
Victoria, B.C.
Canada