Time Zones?

[Craig]

Hi Folks,

I have a little bit of a problem with my hosting company. The long
and short of it is that they are a US based company and my website is
intended for a UK audience.

When getting dates and times from the SQL server attached to my
hosting account, using VS2005 and asp.net 2.0, it renders the date
correctly, but the time is the actual time in the states.

So a recordset might return 0430 which is actually 1130. Is there
any way I can apply some formatting to this to force it into the
current UK time (either GMT or BST (GMT+1)). I don't want to just add
7 hours on, because that looses daylight savings.

I dont mind whether the formatting is done on a page level, part of
the stored procedures I use, or better still something I can change on
the database itself!

Any ideas?


Craig.

 

[Guest]

Dear Craig,

I think if you use the DATETIME function to get the dates and times for SQL
server, it will get the dates and times from the datetime setting on that
server. So it may have difference between your server and the hosting server,
therefore, it need to ADD 7 hours into the datetimes, i think. Maybe, you can
write a sql server function, say GetRealTimes to get the real times, then you
can use this function in all stored procedures.

Hope this can help you!

Regards
Joe Tsui

 

[Otis Mukinfus]

Hi Folks,

I have a little bit of a problem with my hosting company. The long
and short of it is that they are a US based company and my website is
intended for a UK audience.

When getting dates and times from the SQL server attached to my
hosting account, using VS2005 and asp.net 2.0, it renders the date
correctly, but the time is the actual time in the states.

So a recordset might return 0430 which is actually 1130. Is there
any way I can apply some formatting to this to force it into the
current UK time (either GMT or BST (GMT+1)). I don't want to just add
7 hours on, because that looses daylight savings.

I dont mind whether the formatting is done on a page level, part of
the stored procedures I use, or better still something I can change on
the database itself!

Any ideas?


Craig.

Here is one way to do this (untested).

Get the time from the server (DateTime ServerTime).

Display: string.Format("{0}", ServerTime.UTC);

I'm assuming that as you say, UTC time will be ok.


Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com

 

[Joey]

Don't forget about DateTime.UtcNow instead of DateTime.Now. Also MS SQL
has the GETUTCDATE() function instead of GETDATE().

 

[Bruce Barker]

the following will get the system hour offset from gmt,

datediff(hh,getutcdate(),getdate())

use dateadd with this value, plus your timezone offset from gmt and your
done. you could write a simple user defined function to convert server
datetime to your local datetime.

-- bruce (sqlwork.com)

 

[Craig]

Thanks folks - I have used different bits of each of your suggestions
in different places across my project. Thank you.