CODEWITHSUNDEEP
sql-serverdatetime
Andrzej Łukasiak
Andrzej Łukasiak

Reputation: 1

How to set datetime in MSSQL

Is there any way to change SQL server datetime? GETDATE() and CURRENT_TIMESTAMP show the wrong dates.

I read that SQL reads the date from the server settings where the SQL instance is installed. But on this server(Windows SERVER 2019) time, date and timezone are correct. Only in SQL Server are those settings wrong. I've tried to find a solution for this issue over internet but I couldn't find any.

Only SYSUTCDATETIME() shows correct information.

Upvotes: 0

Views: 995

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46241

It seems the server timezone was changed since the SQL Server instance was started. Run the query below to verify the timezone SQL Server is currently using:

SELECT CURRENT_TIMEZONE();

If the result is different than the OS configuration, restart the SQL Server instance for the new timezone to become effective.

Upvotes: 1

Thom A
Thom A

Reputation: 95906

GETDATE() and CURRENT_TIMESTAMP will be working correctly. They return the server's local time. If the time is "wrong" then it's because the time on the server is "wrong"; most likely because it thinks it's in a different timezone to where it physically is or has the wrong DST setting.

Clearly, however, the time is correct on the server for its location (in the sense of that if it's observing CET it would display 11:38 around now) as you state that SYSUTCDATETIME() returns the correct UTC time.

if the server does have the wrong time setting, however, the fix is fix the time on the server. Speak to your server administrator about that. YOu change change the values GETDATE() and CURRENT_TIMESTAMP return as they are based on the host's time. This is why I am confident the time is correct, likely it is either set to the wrong timezone or DST setting as the UTC time is correct.

If, however, the time is correct for where the server is physically located, then the answer is don't use GETDATE() or CURRENT_TIMESTAMP to get the value for your local time, instead (like you have) use SYSUTCDATETIME() or SYSDATETIMEOFFSET() and then convert the time to your timezone in the application layer.

Upvotes: 1

Related Questions