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.
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.
ste5an
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ?
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 ?
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.....
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.....
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...
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...