Any function in sql server which converts the UTC dates to locat time? 1

[rjohn2001]

Hello

Is there any function in sql server which converts the dates(UTC) to locat time.

problem:

I have let say 20 users and each work in different timezones and each user should be able to see their data in their timezone and the rest will be in their respective timezones.

i don't know what is the best way to do this.

I am storing the dates in uST format and storing the timezone information for each user in the table againest the user and thought this was the best way to do this.

i am stumped with the daylight saving timings as well.

can some one please let me know whether any UDF function to convert this dates.

Thanks

 

[bborissov]

Check

http://www.codeproject.com/useritems/ConvertUTCToLocal.asp

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[rjohn2001]

Thanks Borislav,

But unfortunately this won't help me in my case, as the same user might travel to different timezones but he expect the data to be viewed in his choosen timezone.

 

[bborissov]

With His/Her own notebook
Check

http://weblogs.asp.net/sbehera/archive/2006/01/20/435994.aspx

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[rjohn2001]

Borislav,

I think the solution you have pointed to me is discussing about Time Conversion on .Net. It is also reading the TimeZone information from the registry.


I am looking for a solution using sql server
(I hope any expert might have written a function or stored procedure to translate UTC dates to a local TimeZone) as we just had to translate the dates on the server and
display it to the user depends on the user TimeZone(which is predefined against the user)

any help greatly appreciated. Hope this will be a common problem for many though i did not find any good solution to solve this problem.

Thanks

 

[gmmastros]

Most people know about GetDate() to return the server's current date and time: Select GetDate()

Also true... Most people don't realize that there is a GetUTCDate() function built in to SQL Server: Select GetUTCDate()

Want to know the time difference between your server and UTC time?

Select GetUTCDate() - GetDate()

On my computer, the result is...
1900-01-01 04:00:00.000

I don't know if this is what you are looking for, but hopefully this will help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rjohn2001]

Hi George

You are right it will give you the UTC offset, but my problem is, this shouldn't be client specific , but instead it should take the Timezone settings against the user in the table.

If you use GetUTCDate() - GetDate() - it will give different offsets for different timezones. In general this is correct.

But this doesn't work if i have assigned timezones to users, and they expect to see the data in their predefined timezones where ever they travel.

And also this will not address the daylight saving time problem.

What i am trying to do is i want to show the appointments made to different users across the world.

Thanks

 

[gmmastros]

The problem is that SQL Server does not have access to the users system (where their time zone information is kept in the registry). Are you storing timezone information in the local database? If so, how?

There are only 2 ways that I can think of that will allow you to create a formula to calculate their local time.

1) Store timezone information in the database (with an hour offset). Then, simply add the offset when calculating their local time.

2) Pass the timezone offset in to the query so you can perform the calculation.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rjohn2001]

Hi George

I am storing the Timezone info in the database along side each user.

As we know what Timezone the user wants to be in , so the information of the offset

But this offset changes when the Daylight saving time changes.

so problem is how do we determine the offset taking into consideration of DST dates.

I don't want to read from the registry as this will be different when the user moves to another Timezone.

thanks

 

[gmmastros]

Daylight savings time does add to the complexity. So, my suggestion is to get the time offset (between UTC and their local time) using whatever front end language you use. Then, when the user logs in to your database, store this value in a table so you can easily use it to adjust the time.

Here's an article that explains how to do this in VB:

http://www.vbrad.com/article.aspx?id=62

Other than that, I'm stumped. Good luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rjohn2001]

Hi George

This will work perfectly, if we wanted to display the local time based on local timezone

But my Main problem is

i know how to calculate a daylight saving time based on the local time but how do we calculate the daylight saving time for a perticular timezone


I think i should have been clearly explained, what my actual problem is

These timezones are fixed for each user so shouldn't change when the user move to another location unless they change their timzone via their application.

I am storing the UTC offset for each user in the table, but the problem is how do we calculate the daylight saving time on a perticular date range for a given timezone.

here is my table structure

Users
-------
UserId TimeZoneId
1 1
2 1
3 2
4 3


TimeZones
---------
TimeZoneId TimeZoneName Offsetinminutes
1 GMT 0
2 PST -480
3 EST 600


suppose if i have made some appointments with these users, they should display in their corresponding timezones irrespective of where ever they go.

generally what they want to do is they want to see their appointments on a perticular date range(let's say appointments over a week or a month etc)

so in order to show them the correct timings in their appointments we must take care of the daylight saving time as well

so hope i am clear in what i am looking for. sorry for any earlier confusion if i created in explaining my problem.

Note: I am using using the fron end as asp

hope the experts here will shed some light in to this and pull me out of this problem

Thanks