Solved

SQL Server time zone

Posted on 2015-01-21
8
702 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
  • 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In this article I will describe the Copy Database Wizard 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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now