Menu
I am building a web application where users can enter events, including
The users would like to enter the start date/time including a timezone that corresponds with the location of the event. The events are worldwide so the timezone can change from event to event.
In the SQL Server backend database, I am using datetime for the start date/time. What column type should I use to store the timezone? int? float? decimal?
692
USE time_converter; Step3: Creating table times with 2 columns using the following SQL query as follows. Let us create a table with index and DATETIME as a datatype. CREATE TABLE times (Sno INT, date_time DATETIME);
For SQL Managed Instance, return value is based on the time zone of the instance itself assigned during instance creation, not the time zone of the underlying operating system. For SQL Database, the time zone is always set to UTC and CURRENT_TIMEZONE returns the name of the UTC time zone.
2 Answers. SELECT GETDATE() AS CurrentTime, GETUTCDATE() AS UTCTime. SELECT DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), YOUR_DATE);
Timezones are tricky, evil things. They're normally stored as a UTC offset, but even that has issues with regards to things like when daylight savings times change over (if at all).
If you're using Sql Server 2008, you can use a datetimeoffset type, which includes the utc offset with the value. Otherwise you'll need two columns.
158
Since you are using SQL Server 2005, I would recommend storing the time zone as a string in the database, specifically a 32-character string since that is the limit on length for time zone identifiers in the Windows registry.
The values saved should be the values from the TimeZoneInfo ID property (e.g. "Eastern Standard Time") so that you can do calculations in the .NET Framework more easily.
As Joel said, time zones are evil and tricky. Good luck...
28
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
