Adding an offset to datetime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Adding an offset to datetime

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2009-02-07 : 22:12:25

I have two columns datetimeUTC (DateTime) and Offset (nvarchar(5)). What is the most effective way of provding a new datetime (DateTime) in SQL 2005 for the client to display?

datetimeUTC Offset Expected Result
8/02/2009 11:00:00 AM 0 8/02/2009 11:00:00 AM
8/02/2009 11:00:00 AM +2:00 8/02/2009 1:00:00 PM
8/02/2009 11:00:00 AM -2:00 8/02/2009 9:00:00 AM
8/02/2009 11:00:00 AM +2:30 8/02/2009 1:30:00 PM
9/02/2009 11:00:00 PM +2:00 10/02/2009 1:00:00 AM
10/02/2009 1:00:00 AM -2:00 9/02/2009 11:00:00 PM

I can store the Offset in any datatype, maybe best as bigint then stored in seconds?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-07 : 23:57:54

We store the offset as an int in the forum of minutes. Such as -60 or 90. I don't see why we are using int though as we could use an even smaller data type.

I wouldn't recommend using bigint here as it is not necessary to store seconds. Offsets can be handled in minutes easily.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2009-02-08 : 00:14:07

So if it was as below how do we handle the offset calculation:

datetimeUTC Offset Expected Result
8/02/2009 11:00:00 AM 0 8/02/2009 11:00:00 AM
8/02/2009 11:00:00 AM +120 8/02/2009 1:00:00 PM
8/02/2009 11:00:00 AM -120 8/02/2009 9:00:00 AM
8/02/2009 11:00:00 AM +150 8/02/2009 1:30:00 PM
9/02/2009 11:00:00 PM +120 10/02/2009 1:00:00 AM
10/02/2009 1:00:00 AM -120 9/02/2009 11:00:00 PM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-08 : 00:52:31

The maximum offset is -12 hours or + 12 hours, which is 720 minutes.
Use SMALLINT and as tkizer wrote, minutes is enough.
There are some parts of the world that changes offset to an half of an hour (30 minutes) during summer time. These countries mostly reside in the Middle East.

E 12°55'05.63"
N 56°04'39.26"

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-08 : 01:08:57

You can either add a computed column to the table or perform the calculation dynamically in a query:


Computed Column:
CREATE TABLE Table1
(
	DateTimeUTC	datetime,
	OffsetMin	smallint,
	DateTimeLocal	AS DATEADD(minute, OffsetMin, datetimeUTC) 
)

Query:
SELECT datetimeUTC, OffsetMin, DATEADD(minute, OffsetMin, datetimeUTC) as DateTimeLocal
FROM Table1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources