I have some issue which needed to be fixed quickly. My local development server is in middle east. But my production server is in UK. Now, I need to show the date to user to thier timezone. For example, if a user is in Saudi Arabia, then I need to show the time according to Saudi Arabia format. Should I need to create a new database table called TimeZone and I will save the Time in UTC. Please suggest me/
There's no quick fix for this, unfortunately. Internationalization of an application should be part of the very first design discussions, as it really goes to the core of a lot of different areas, including date/time comparisons and output formatting.
Anyways, to get on the track of Doing It Right, it's essential to store the time zone information with the time. In other words, realizing that the date/time
In SQL Server 2008+, you can store the time zone with the time directly by using the
This makes it easy for a desktop-type application, as these platforms normally have mechanisms to automatically convert a date/time + time zone to a local time and then format for output, all based on the user's regional settings.
For the web, which is an inherently disconnected architecture, even with the back-end data set up properly, it's more complex because you need information about the client to be able to do the conversion and/or formatting. This is usually done via user preference settings (the application converts/formats things before output), or simply showing things with the same fixed format and time zone offset for everyone (which is what the Stack Exchange platform currently does).
You can see how if the back-end data is not set up properly, very quickly it's going to get complicated and hacky. I would not recommend going down any of those paths because you'll just end up with more problems down the line.
SQL Server made modifications in 2005 onward where the internal timezone is saved in UTC. This was largely due to geo-replication and HA projectors involving log shipping, and having the log shipping times saved in different time zones made it impossible for the old method to restore them.
Thus, saving everything internally in UTC time allowed SQL Server to work well globally. This is one of the reasons why daylight savings is kind of a pain to deal with in Windows, because other MS products such as Outlook also save the date/time internally as UTC and create a offset that needs to be patched.
I work in a company where we have thousands of servers (not MS SQL Servers though, but all kinds of servers) spread out all across the world, and if we didn't specifically force everything to go by UTC, we would all go insane very quickly.
I have developed and published the “T-SQL Toolbox” project on codeplex to help anybody who struggles with datetime and timezone handling in SQL Server. It’s open source and completely free to use.
It offers easy datetime conversion UDFs using plain T-SQL with additional configuration tables out of the box.
In your example, you can use the following sample:
This will return the converted datetime value for your user.
A list of all supported timezones can be found in table "DateTimeUtil.Timezone" also provided within the T-SQL Toolbox database.