what is wrong with the date and time in SQL server 2016?

Joshua Tay 246 Reputation points
2021-08-30T03:32:20.117+00:00

Hi,

I'm trying to get the time stamp of my users login in and out of my ERP system.

I'm using this code:

SELECT [User ID], [Client Computer Name], [Event Datetime],
CASE
WHEN [Event Type] = 0 THEN 'Logon'
WHEN [Event Type] = 1 THEN 'Logoff'
WHEN [Event Type] = 2 THEN 'Start'
WHEN [Event Type] = 3 THEN 'Stop'
WHEN [Event Type] = 4 THEN 'Close'
ELSE CAST([Event Type] AS VARCHAR)
END AS [Event Type]
FROM [Session Event]
ORDER BY [Event Datetime] DESC

When I pull out, the date and time comes out like this:

28/8/2021 2:26:13 am

Nothing wrong with it, except that we don't work during night time.
But all the timestamps are in the wee hours. It doesn't reflect accurately our working time which is 9am-6pm
i suspect its either the timeclock in the server is wrongly, but I have checked that the server time syncs accurtely as my laptop time.

Can anyone advise why is this happening?

Thank you!

Josh.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,482 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 117.1K Reputation points MVP
    2021-08-31T06:45:11.39+00:00

    The problem is that we don't know much more about this than you do. We can guess that the time is in UTC, but that is just a guess.

    I would suggest this: first run "SELECT sysdatetime()" to verify that you get back the time in your time zone. Once you have done this, contact the vendor for you ERP system to ask what they are up to.

    1 person found this answer helpful.

5 additional answers

Sort by: Most helpful
  1. Olaf Helper 46,041 Reputation points
    2021-08-30T06:32:14.687+00:00

    Yes, the results are the same even in the Management Studio, I wonder if its due to my server clock?

    How to say, if we don't know how you generate/insert the data? are the data inserted from a client with local timestamP or server side?


  2. AmeliaGu-MSFT 13,991 Reputation points Microsoft Vendor
    2021-08-30T08:06:17.747+00:00

    Hi Joshlabtech-6194,

    How do you capture the login and logout information?
    The timestamps in the various logs are different. Some times are local to the SQL Server instance, others UTC and yet other utilities attempt to adjust the UTC time using your current client settings.
    Please refer to Correlating Timestamps From Various Data Points which might be helpful.

    Best Regards,
    Amelia


  3. Olaf Helper 46,041 Reputation points
    2021-08-30T12:04:54.893+00:00

    FROM [Session Event]

    There is no system / build-in table named [Session Event], so it must be a user-defined table.
    In SSMS do a right-mouse click on the table => "View dependencies" to get a referencing objects, may there is a stored procedure/trigger filling the table.


  4. Tom Phillips 17,741 Reputation points
    2021-08-30T12:43:05+00:00

    Just to be clear.

    There is nothing wrong with SQL Server. Your APPLICATION is storing the datetime in a specific time zone, likely UTC time zone.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.