SQLServer and datetime and UTC

By glynlewis99 ·

I am in the middle of a debate at work about the merits, or otherwise,of storing all datetimes in the databases as UTC (what us old boys used to call GMT).

What I am trying to get to grips with is how storing dates as UTC does one overcome local time issues such as different time zones (this is Australia and we have a number of time zones) in an efficient manner i.e. some transactions will occur in one state at UTC +X hours and another state ay UTC +Y hours and need to be displayed in a state that is operating at UTC +Z hours - if the date is universally stored as UTC how do we know what the local datetime was at the transaction source when the transaction took place.

The software apps guys are saying they will develop functions to convert the stored UTC to local time, however this suggests to the user that the transaction occured at a certain time, when in reality it was another time in a different timezone where the transaction physically took place. I'm looking at how would one write reports in an efficient manner i.e. I wouldn't want to execute a datetime conversion for each and every row returned by the SQL or include a complex CASE statement.

BTW the database is SQLServer and I do know that internally SQLServer stores dates as a binary number +/- 1900 - this question is about how can one use ETC as a standard point in time referene for transactions that will be loaded into the DB that occured at varying GMT+ hours and present them in consisent way across all applications?


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Share your knowledge

Related Discussions

Related Forums