• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2020
  • Last Modified:

MSSQL timezone specific date

Hi Guys,

I would like to know if i get MSSQL to return the current time in a specific timezone.

I would like a function like this :

castFromServer ( getDate , "the_timezone_I_am_after"  ).

I have goggled a few resources to no avail.

Any assistance is greatly appreciated.

Cheers,
0
sydron
Asked:
sydron
1 Solution
 
frankyteeCommented:
i'm not aware of any built in timezone calculator in sql. you'll have to create a "lookup" table with schema like:
City
Country
GMT (time relative to Greenwich Mean Time)
TimeRelativeToLocalTime - optional numeric field- like +5 hours, -9 etc

then join this table on the city country to get the GMT or relative time to your local time.
google to get a site which shows all the major cities and their timezones to build your table.
0
 
Patrick MatthewsCommented:
Hello sydron,

Building on frankytee's response, take care to account for Daylight / Summer Time.  Windows has some
APIs that convert local time to GMT and vice versa; you may be able to exploit that using the new SQL
Server design studio...

Regards,

Patrick
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
Along all the lines.  you would have to be able to get the timezone offset in hours that it represents.
You can get the Hours offset by using

DATEDIFF(hh, getdate(), getutcdate())

This is the amount of hours you are behind GMT, then you would keep that information and use it.

0
 
Patrick MatthewsCommented:
dbaduck,

dbaduck said:
>>DATEDIFF(hh, getdate(), getutcdate())

Small tweak: if you are going to use DATEDIFF, which makes sense, it is better to use minutes instead
of hours: some time zones have a bias on the half hour, such as in India.  You would also still have to know
whether Daylight Time or Summer Time is in effect, and the rules of course vary widely across
jurisdictions.

Regards,

Patrick
0
 
sydronAuthor Commented:
Damn, i thought this would be easier and that MSSQL would have something for this.

Thanks for your help guys...You have lead me in the right direction. I will try the ideas out and get  back to you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now