get timezone specific date

Hello,

I am struggling with one algo i.e. I want to retrieve the date according to time zone

I have table called TIMEZONe in which i have the information about the hours difference (+12 to -12) from GMT...

now my scenario is that I want to get the date, using information from timezone area table (time difference from GMT), of any particluar time zone

can any one tell me, how will I able to achieve this task.

for example:

it 4:00 AM in the mornign in GMT TIME and date is 11-08-2006 but in US it 11:00 PM and date is 10-08-2006

Note: Server date is set to GMT (System date)

regards,

Anas

i HAVE DEVISED THE FOLLWING ALGO

IF @.OFFSET - DATEPART(HH,GETDATE())>=0

SET @.SYSDATE = GETDATE()

ELSE

SET @.SYSDATE = GETDATE()-1

PRINT @.CDATE

WHERE @.OFFSET IS NO. OF HOURS DIFFERENCE FROM GMT

ANY BETTER SOLUTIONS ARE WELCOME

REGARDS,

ANAS

|||

Where is the problem? If you have a TimeZone table you could use a query like this

select dateadd(hour, TimeZone.GMTDiff, getdate())

From TimeZone

Where Zone = 'US'

|||

THANX FOR YOUR BETTER SOLUTION

I DIDNT TRY THIS

I WAS USING DATEDIFF BUT COULDNT SOLVE THE PROB.

THANKS AGAIN