[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1408
  • Last Modified:

SQL Server time zone

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
gdemaria
Asked:
gdemaria
  • 2
  • 2
  • 2
  • +2
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
OriNetworksCommented:
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
 
gdemariaAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
OriNetworksCommented:
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
 
gdemariaAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
_agx_Commented:
(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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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