Solved

SQL Server time zone

Posted on 2015-01-21
8
1,115 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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
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 66

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

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