Solved

SQL Server time zone

Posted on 2015-01-21
8
1,009 Views
Last Modified: 2015-02-05
I have a  SQL Server 2012  database on Amazon RDS.    Amazon does not allow you to change the timezone at the server level, so anything that requires time is viewed as GMT time instead of my local zone.  They said it has to be done at the application level.

Is there a way I can automatically run a procedure, function or command to set the time zone with each database session opened?

My primary access to the database is through Coldfusion programming language, that is where I really need the time zone to be changed.
0
Comment
Question by:gdemaria
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 250 total points
ID: 40562570
you need to use, getUTCDATETIME () instead of Getdate()
also SQL  2008 has a type called datetimeoffset. It's really useful for this type of stuff.
http://msdn.microsoft.com/en-us/library/bb630289.aspx
Then you can use the function SWITCHOFFSET to move it from one timezone to another, but still keeping the same UTC value.
http://msdn.microsoft.com/en-us/library/bb677244.aspx
0
 
LVL 17

Assisted Solution

by:OriNetworks
OriNetworks earned 250 total points
ID: 40562576
I don't think there is a global way but you could create a function to format the data in your queries. Typically it is best if the date is stored as UTC to begin with for easier formatting across timezones although you can still get away with it if you only use 1 timezone.

form example if the new sql function receives a parameter for datetime and a timezone offset, the function could simply say subtract x number of hours from the datetime value to convert it to my local time.

In your sql queries is would be use like this:
Select id, myfield1, dbo.fnTimezoneShift(myDateField,3) from table1

Open in new window

0
 
LVL 39

Author Comment

by:gdemaria
ID: 40562585
I'm trying to avoid changing hundreds of files of code, these solutions seem to require me to alter a lot of code.

Any chance I can overwrite the getDate() function to use my local getDate() instead of the system one?

Something like that...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:OriNetworks
ID: 40562655
Unfortunately not. With php/MySQL there is a way to alter the timezone with each connection here but that doesn't seem to apply to you.

Sorry to see you've run into a difficult situation.
0
 
LVL 39

Author Comment

by:gdemaria
ID: 40562671
Thank you Ori.  

Rereading Aneesh's answer.  I am wondering if I can change the data type on my table columns to datetimeoffset with the default offset being my timezone... am I understanding that correctly?  

Do you think I can change the data type from dateTime to datetimeoffset and keep my date/time in tact?  Would that work?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40562834
You should be able to do it. One important point while updating the data is the day light savings time.  I will probably keep this field and add another column to store the timezone data.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40562838
Expanding on Ori's first comment, I had a client that was an airline that had a real need to convert time zones / daylight savings time, and they way they pulled it off was also a function, but with an additional parameter AirportCode.

The AirportCode was a PK in an AIrport table of airport codes that had columns of Time Zone ID, and a Time Zone table had the UTC offset, daylight savings time start, and daylight savings time end.   Grab those values, do some DATEADD(hour, whatever) and bingo bango there's your conversion.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40563536
(no points... I'm not familiar w/the new data type)

>> change the data type from dateTime to datetimeoffset and keep my date/time in tact

I'd make a backup first to be safe. Also, be sure to test it out thoroughly with CF's jdbc driver, cfqueryparam and date functions first. Make sure the driver handles it properly and doesn't do anything wonky like convert the values to string or something...
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
disk usage reporting tools 27 56
SQL Server Express or Standard? 5 32
Running Total Using new MS SQL Function 21 52
Merge join vs exist 3 27
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question