Timezones in sql server 2012

I have sql server 2012, where I Stored times zone for users, so that I can convert the datetimes to their timezones before showing it
 I stores date in UTC format.

I need to know how can I convert UTC dates to our customer's time zone lile CST or Indian Standard time.
yadavdepAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
You should review your architecture. Cause the conversions are done in the front-end, cause this is the place where you know the target time zone. E.g. when you have a properly implemented ASP.NET / IIS front-end, then this should already happen automatically. Otherwise you must look at the localization of the front-end.
1
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> I need to know how can I convert UTC dates to our customer's time zone lile CST or Indian Standard time.

Since you are storing the date in UTC values, you can go with the below approach..
1. From the application get the local time value.
2. From the database, get the GETUTCDATE() value.
3. Identify the difference between these 2 values and then add/subtract the minutes value to get the UTC date value converted to local time zone like CST or Indian Standard Time.

As explained in the approach, your application should be able to do these manipulations to get it work better.
0
Mark WillsTopic AdvisorCommented:
How are you storing timezone by user ?

How do you identify a user when they access the server - what credentials - Via applications logging in / web pages / (in)direct access ?

The very big challenge is getting any date / datetime may have to go through a conversion process. Thats why it is very often up to the front end, the part that the users have direct interaction with...

If you can give us more information, we can be more specific....

Are you expecting to be able to do this at the server level, or, do you have code at the front end ?
0
arnoldCommented:
To what others have suggested, are you getting/storing the local user time zone in a cookie, variable within the database such that when the user accesses and queries, part of your setup has
the dateadd (h,column,@usertzoffset)
when the data is returned, the user sees the info in their local.
Much depends on how your presentation works and processing.
i.e. do you want this processed on the sql server as above. on the web server where the conversion might be with more overhead, or on the client side if you are using ajax, jquery, dynamic .....

Look at any site that aks you to personalize such as EE.
You are storing data in UTC the conversion
Do you want to use GEOLOCATION identification based on the source IP from which you are getting the request to guestimate the timezone and thus the @usertzoffset.....
0
Mark WillsTopic AdvisorCommented:
Hello ?

Hope we havent scared you off.... Would like to help you with your problem.

A few questions have been raised above, it would be good to hear back from you...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.