adding timezones to dates/time

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 2000 Forums

SQL Server Development (2000)

adding timezones to dates/time

Author

Topic

cire
Starting Member

15 Posts

Posted - 2006-03-31 : 02:26:07

Hi all, i got a problem with dates and timezones which i hope your can help me solve. At the moment i'm using dateadd(ss,open_date,'1970-1-1') which outputs the date and time without the timezone offset. i need to include the timezone diff, apparently open_date is set according to gmt+0. furthermore this open_dates are open_dates of tickets created by different users. different users have different timezones. Their assigned timezones are placed together with rest of their details in another table called contact. All the various timezones are stored in another table called timezones(duh)

as i'm inheriting this database so i'm not very familiar with the database structure. All i noe is that the open_date is in LOCAL_time-Integer format, timezone is in String format. So i was wondering if i can do convert the string to integer then add them together and return that output. If this is possible, how can it be done?

many thanks
Eric

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-31 : 02:55:06

Something like this ?

select	dte as [Date @ GMT+0], time_zone, dateadd(hour, convert(int, replace(time_zone, 'GMT', '')), dte) as [Date @ TZ]
from
(
	select	dateadd(second, open_date, '19700101') as dte, time_zone
	from
	(
		select	1143820035 as open_date, 'GMT+0' as time_zone	union all
		select	1143820035 as open_date, 'GMT+8' as time_zone	union all
		select	1143820035 as open_date, 'GMT-8' as time_zone	union all
		select	1143820035 as open_date, 'GMT-12' as time_zone	
	) a
) b

KH
Choice is an illusion, created between those with power, and those without.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources