Need Time zone as a part of date/time

[spaliwal]

Hi,

I am using sql server 2000. I need date/time with time zone like "MM/DD/YYYY HH:MI:SS ZZZ" where ZZZ may be PST , IST etc, but while seeing datetime or smalldatetime datatype i could not get any style code to get time zone as a part of it.Pl. help me to get it by anyway.

Thanks in advance,

Sheilesh

 

[rt63]

A possible workaround could be to create a master table of various timezones and their time difference with your standard time.

create table mst_timezones (zone_cd char(3), offset int)

In this table you can store time difference in (say minutes). So let us say your default is GMT and you want to add an entry for time zone IST (which is 5hr,30 min ahead of GMT). So you can add an entry

insert into mst_timezones ('IST',330)

When you display, the datetime in the desired format, you will have to apply a join with this table (say you want in IST format)

select convert(varchar,dateadd(mi,-offset,<date field>),120)+' IST'
from <date field table> , mst_timezones
where mst_timezones.zone_cd = 'IST'

I hope it helps.
RT

 

[spaliwal]

Dear RT,

Thanks a lot, Yeah i am very much agree with u. But instead of keeping time difference from mine time zone , I would like to to keep time zone differece from GMT. Since i can get GMT time directly by using GETUTCDATE() and from table mst_timezones , i will get the desired Time zone time.

Thanks for such a prompt reply and valueble suggestion.

bye,

Sheilesh