Convert UTC to Local time or retrive TimeZone / LocalTime from audit records?

Question

Hello,

I have enabled auditing in SQL 2008 and retrieve audit records via sys.fn_get_audit_file(). It returns the "event time" (in DateTime2) in UTC date & time, as opposed to local date &  time. How can this UTC date & time be converted to local time?

Local Time depends on the (1) TimeZone and (2) Day Light Saving adjustments and this makes writing custom routines difficult.

Is there a standard way of doing this? I noticed that this question has been asked before (regarding previous versions of SQL Server) and answers ranged from writing custom sprocs/udfs to CLR functions.

Ideally, auditing functionality should have recorded the "Local Time Offset" along with the "event time". Has anyone come across this situation and found a work around?

Thanks

Answer 1

honestly I haven't used these datatypes before but this seems to work:

DECLARE @localzone varchar(6) = RIGHT(SYSDATETIMEOFFSET(), 6)
SELECT @localzone
DECLARE @dto1 datetimeoffset = '2011-02-02 10:00:00 +05:00'
SELECT @dto1
DECLARE @dto2 datetimeoffset = SWITCHOFFSET(@dto1, @localzone)
SELECT @dto2
DECLARE @dt2 datetime = CONVERT(datetime, @dto2)
SELECT @dt2

Answer 2

Hope the below forum links helps

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/1076202c-60b8-4798-bf9e-ac6f012f3aac

 

Answer 3

dents,

This would have worked if the all the datetimes in audit log have the same offset as the current time. For example, audit log can have datetime before and after the day light saving period kicks in. SYSDATETIMEOFFSET() only gives the current offset, which is not applicable to old dates which have a different offset due to day light saving adjustments that happen twice a year.

The crux of this problem is to find a function that can inspect a given datetime in UTC and using the region settings, find out which offset was applicable to that time.

Alternatively, Auditing functionality should have saved the time offset along with UTC, so that those datetimes can reliably convert to local times.

Answer 4

Sorna Kumar,

Please see my reply to dents's response. In short, this suggestion will not work if audit datatimes' are of a different offset to current system offset. This is the case with day light savings which happens twice a year.

Answer 5

Yeah , I got your point... I have found the below thread for computing the UTC based on the daylight saving also... Try this

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28712

Answer 6

Thanks Sorna for that.

In summary, the solution posted in the above thread is to store daylight saving start and end dates in a look up table. The unfavourable aspect of this approach is the continous maintenance (adding dates as time passes by). It is little work, but someone has to remember to do it.

I will take this as the a solution. Thanks.