August 27, 2012 at 1:29 am
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
August 27, 2012 at 2:32 am
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.
August 27, 2012 at 2:39 am
I'am recording my timezones.
August 27, 2012 at 2:51 am
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
August 27, 2012 at 2:56 am
I'm saving the time of which the record gets saved by GETDATE() function. It is taking server time only not location time
August 27, 2012 at 3:08 am
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.
August 27, 2012 at 8:11 am
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 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/
August 27, 2012 at 9:48 am
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
August 27, 2012 at 2:17 pm
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply