[dba-SQLServer] UTC Date Time Fields and Baan with SqlServer

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

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com