Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Issue with GETDATE() Expand / Collapse
Author
Message
Posted Monday, August 27, 2012 1:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 5, 2013 11:16 AM
Points: 8, Visits: 55
Hi all,
We have a server in Sydney and users across India and US. When ever a record is gets saved by a user we use GETDATE() function for audit trails.

And my problem I want to save the time based on region (currently Sydney time is getting saved for all the users).

Can anyone help me in this regard.


Thanks in advance
Phaneendra S
Post #1350231
Posted Monday, August 27, 2012 2:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 12:11 PM
Points: 9, Visits: 70
Are you recording timezones anywhere in your audit trail? If not, having the times recorded in differing timezones is going to leave your audit trail useless.
Post #1350261
Posted Monday, August 27, 2012 2:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 5, 2013 11:16 AM
Points: 8, Visits: 55
I'am recording my timezones.


Post #1350264
Posted Monday, August 27, 2012 2:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 12:11 PM
Points: 9, Visits: 70
How are you generating the audit trail?

EDIT: If you know the timezone, then you should know the offset. Use TODATETIMEOFFSET:

http://msdn.microsoft.com/en-us/library/bb630335(v=sql.100).aspx
Post #1350269
Posted Monday, August 27, 2012 2:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 5, 2013 11:16 AM
Points: 8, Visits: 55
I'm saving the time of which the record gets saved by GETDATE() function. It is taking server time only not location time
Post #1350270
Posted Monday, August 27, 2012 3:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 12:11 PM
Points: 9, Visits: 70
It sounds like you're using a trigger, which won't have a clue as to what timezone the client is located.

Since you already have the timezone offset, don't use GETDATE. Use the offset function I listed above.
Post #1350272
Posted Monday, August 27, 2012 8:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:54 PM
Points: 13,471, Visits: 12,329
Chow's (8/27/2012)
I'm saving the time of which the record gets saved by GETDATE() function. It is taking server time only not location time


GETDATE() is a sql function. It will ALWAYS return the time of the server. It does not have any knowledge of the timezone your client application is running in. As previously suggested you should look at datetimeoffset or somehow record how to make the adjustment in relation to your server.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1350409
Posted Monday, August 27, 2012 9:48 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
I prefer logging data with the UTC date and time. That way, servers in different time zones don't mess up their date/time sequence. Let the UI do the localization, including converting to local date and time.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1350458
Posted Monday, August 27, 2012 2:17 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 11, 2014 3:04 PM
Points: 2,127, Visits: 747
Use the GETUTCDATE() function instead of GETDATE() and consider using the datetimeoffset data type to store your values WITH their timezone offsets. Then you can use a simple function in your queries to synchronize the results correctly.


Post #1350575
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse