‘Date’ and ‘Event Time’ columns in an Audit log in SQL Server are displayed in UTC

When you open an Audit Log, you may notice that the values for the Date and Event Time columns for the events that just occurred are different from the current Local time (if your Local time has time offset from UTC time):

Date and Event Time columns in an Audit log in SQL Server are displayed in UTC.

To see your current time offset from UTC time run this code:

SELECT DATEDIFF (hh, GETUTCDATE(), GETDATE())

To get the Local time from UTC time, add your time offset to the UTC time:

DECLARE @UTC_time datetime
SELECT @UTC_time = GETUTCDATE()
SELECT @UTC_time AS 'UTC Time'
,DATEADD(hh, DATEDIFF (hh, GETUTCDATE(), GETDATE()), @UTC_time) AS 'Local Time'

This simple code adds your current time offset to the UTC time, it is not applicable to old dates which have a different offset due to Daylight saving.

If the post helped you, please share it:
Pin It

3 comments to ‘Date’ and ‘Event Time’ columns in an Audit log in SQL Server are displayed in UTC

  • arun

    hi tks for the query

    i have a problem..
    my sql audit log time shows 6:30 AM instaed of 2:30 PM
    my sql agent log and server time log are correct i.e 2:30 pm
    my time zone id UTC + 4
    when i executed the above DECLARE query ,

    now both my sql agent and sql audit log timings modified as 10:30 AM instead of 2:30 PM

    please advice hoe to get fix or alteast revert back my sql agent time stamp

  • jake

    Hi arun,

    the above DECLARE query just gets the CURRENT UTC time and shows the CURRENT Local Time.
    Nothing is modified using the query.
    If you want to see what was the Local time if the UTC time shows for example 10:30 set the @UTC_time variable to the date and time you want, and you will get what was the Local Time at that moment.

  • Anthony Perkins

    Word of caution: For a more general approach use minutes instead of hours. Not everyone is offset by hours, there are some parts of the world where the offset includes fractions of an hour.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">