Enter time in one time zone show in a different

OK this might be a daft question but I’ll ask it anyway

All times are based on a 24 hour clock

What I need to do is collect some data in say Spain and as part of that data they enter a date/time of 31 July 2009 11:00

If someone in Spain looks at the data they will see it as 31 July 2009 11:00

When someone in say the UK looks at the data on screen I need to be able to display it as 31 July 2009 10:00 because the UK is 1 hour behind Spain

If someone in the USA looks at the data they may see something along the lines of 31 July 2009 05:00 (depending on where in the USA they are)

Basically I need to be able to collect a date/time and then show it to users in their local time rather than the time it was entered in the specific country

The data needs to be stored in a SQL database, probably 2005 if that makes any difference

Not sure if I have explained what I am trying to achieve very well but hopefully someone understands what I am getting at.

Thanks.

first i would use .net 3.5 and sql 2008

I did some date and time stuff a while back for an app and it can be a pia.

what you really want to do IMHO is take a local time and convert it to UTC/GMT and store that.

when someone opens you app/web page you can then determine what timezone they are in and adjust to thier local time.

or just show UTC time and make that clear to the users.

why just UTC?

well getting this right is not easy...  in some cases you can do the right math and still have users telling you it wrong!

I have seen it happen...  part of the problem is that there are a whole bunch of different local rules for timezones, rules for when to add and subtract hours for Dalight/std. time and some places do not do that and others do. you can have more than one "timezone" for the same east/west offset from Grenich / 0

so there are places where 2 different clock times are valid depending on how far north you are.

theres i think its called casablanka time that is in offset 0 but it's not the same as GMT - has different rules.

it's a real can of worms to sort it out.

so from my exp. stay as far clear of that as you can... and if you must then be perpared for some hard study and testing.

in general the "rule" is to convert from zone x to zone y you take x and make it UTC/GMT first.

then you take that date-time and convert it to zone y

may sound odd but when i read up on how to do it that was cited as the "right way" as it took the local timne to a "standard" and then took that to some other local form.

like you might be in UTC -5  and they might be in UTC +1 but you have dst offset and they do not... stuff like that.

PS: it's now the "correct" thing to just use "UTC" GMT is an out of date term, folks still use it but some standards thing chnaged it a while back so that it's not seen as "british" and even the letters are they way they are cuase "CUT" would have offened some folks - french bad word i think....  go figure.

There's a DateTimeOffset class in .NET and a datetimeoffset type in SQL 2008 you can use for this scenario.