getDate(), each database is for different timezones

Question

getDate(), each database is for different timezones

alanspeckman

Hall of Fame

Points: 3344
More actions
January 29, 2014 at 2:28 pm

#303520

As part of database cosolidation, I have 4 databases that were hosted on 4 different servers.
Each database is for a different customer in different timezones.
I use to set the timezone for each server and the getDate() function used that time.
Is there a way to set an offset in the database so I don't have to modify the getDate function?
Any ideas are appreciated greatly!!
Alan

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

alanspeckman (1/29/2014)
As part of database cosolidation, I have 4 databases that were hosted on 4 different servers.
Each database is for a different customer in different timezones.
I use to set the timezone for each server and the getDate() function used that time.
Is there a way to set an offset in the database so I don't have to modify the getDate function?
Any ideas are appreciated greatly!!
Alan

You can't change GetDate unless you have the source code for sql server. 😉

As far as your consolidation it is a little difficult to offer solid advice based on the limited information you posted. Maybe you can adjust every datetime value in the system for each customer to adjust all values to the same timezone. Then add a datetime offset value for each customer. Then you can adjust the times to the customers local time when appropriate.

Not really sure what your goal is here as regards to existing data.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Sounds like you should look up the function GETUTCDATE (Transact-SQL) at

http://technet.microsoft.com/en-us/library/ms178635(v=sql.100).aspx

This value represents the current UTC time (Coordinated Universal Time).

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Consider a view or custom UDF in each database that does the calculation. If it's a function, you'll have to pass it a parameter of getdate because of the restriction against deterministic functions (or did last time I checked)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

alanspeckman Hall of Fame Points: 3344 More actions · Answer 4

A Scalar function is what I decided on. to minimize code change, I can call it getDate and then just change the call from getDate() to dbo.getDate(value), where value is the timezone offset maybe. The function simply result of getDate() modified by 'value'.

Thoughts?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Firstly don't call it getdate (even if you can), second I think you'll have to pass two parameters, the offset and the datetime you want the offset to apply to.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

alanspeckman Hall of Fame Points: 3344 More actions · Answer 6

I was just thinking to minimize code changes, but I think you are right. Its for some developers that were worried about making all the changes, so if I said just put a dbo. in front of it, it may be psychologically better for them..........:-D

If I use what bitbucket-25253 suggests, UTC, then I would just need the offset used for that particular database. They have a config table in each database and they will add this offset column to that config table. Then they just pass that config value into the UDF and get back the correct datetime.

thoughts?

Alan.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Looks like the restrictions against non-deterministic functions has indeed been relaxed.

CREATE FUNCTION dbo.OffsetDate(@Offset INT)

RETURNS DATETIME AS

BEGIN

RETURN DATEADD(hh,@Offset, GETDATE())

END

GO

SELECT dbo.OffsetDate(3)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

A change to the function might be needed if the OP wants to include time offsets that don't use full hours as Venezuela which is UTC-04:30.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

alanspeckman Hall of Fame Points: 3344 More actions · Answer 9

Thank you Gail, and everyone for your responses.

Alan.