SQL Server Performance Forum – Threads Archive

Best Practice for dealing with Time Change?

Is there a best practice, when dealing with time changes and time zones, when developing a database?
I see two options.
All time-based data that is being recorded by the Database should be recorded in UTC, and let the front-end developer worry about displaying proper local time of transactions. or Store all times as local times, and end up with two sets of data over the time changes. Is there a better way?
thanks,
Travis
[?]

---

Till now there is no inbuilt option in SQL Server to store the time in UTC.
However, this option only sounds good as it prevents redundancy. Gaurav

---

Gaurav – what do you mean with no built in option to store the time in UTC?
GETUTCDATE() returns a datetime variable that you can store.
or am I missing something… Bambola.

---

Is there an easy way to show dates as LOCAL when returned in a query?
I feel I have to use UTC dates in the database, to avoid duplicate key problems at daylight savings. But I have to show the users the info in LOCAL time, or it won’t make sense to them.
Thanks,
Travis

---

You can check the timezone on the clientside with for example javascript if it’s an unknown user. If it’s a user that have logged in to your site and have a user profile or something you can save their timezone info in a column. Then you just return utcdate + time zone difference. About GETUTCDATE(), it works in SQL 2000 but not in SQL 7. To get UTC date on SQL 7 you can do something like this:
DECLARE @deltaUTCINT
DECLARE @UTCdateDATETIME /* Get UTC time difference on this SQL server */
EXEC master.dbo.xp_regread
‘HKEY_LOCAL_MACHINE’,
‘SYSTEMCurrentControlSetControlTimeZoneInformation’,
‘ActiveTimeBias’,
@DeltaUTC OUT /* Calculate UTCdate */
SELECT @UTCdate = DATEADD(MINUTE, @deltaUTC, GETDATE())
/Argyle

---

Thanks Argyle for the tip. This cleared my doubt. Gaurav

---

Thanks Argyle!

---

]]>