?
Solved

SQL Server time zone

Posted on 2015-01-21
8
Medium Priority
?
1,221 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 1000 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 1000 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

800 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