I am trying to find a way to change SQL server from using UTC time to Local time. This is because I need to be getting Local time when I pull data using ODATA via excel.

Is there a way to configure the SQL server from UTC to local time?

CodePudding user response：

If you have UTC date/time values stored in a datetime, datetime2, or smalldatetime column, you can use AT TIME ZONE to indicate the current value is UTC and to convert the value to the time zone of your choice:

SELECT YourUTCDateTimeColumn AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS YourLocalDateTimeColumn

AT TIME ZONE returns a datetimeoffset data type. This can be cast back to the source type if datetimeoffset is problematic for your use case:

SELECT CAST(YourUTCDateTimeColumn AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS datetime2(3)) AS YourLocalDateTimeColumn

CodePudding user response：

SQL Server's local timezone is derived from the local operating system. If you wanted to globally affect the timezone that SELECT GetDate() uses, then you can change the time zone of the server.

• This is a workaround that is not often recommended to a much larger issue.

Since 2005, the best practice for handling date and time in .Net is to use DateTimeOffset, and this advice became standard in SQL Server 2008. Storing times in DateTime is an anti-pattern that forces a lot of manipulations but worse, it forces a lot of asumptions, for instance how do you know that the value is a local time or UTC, and then which local time zone was used and was it daylight savings or not?

DateTimeOffset provides us with correct sorting and time difference calculations for values that might be entered in different time zones, read more in my blog: Why was DateTime removed from OData v4

If you are querying through an OData API, then you can implement the conversion of timezones in the API logic, or you can manipulate the SQL queries direct, either via middleware, custom serialization or other injection techniques. To go into specifics would however require you to post your associated code.

This answer from @Dan Guzman shows some examples of using AT TIME ZONE in your SQL queries directly, which was introduced in SQL Server 2016. You might also be interested in TODATETIMEOFFSET() or SWITCHOFFSET() but implementing these functions still requires assumptions about the specific time zone to either be hardcoded into the API logic or to be passed through from the client.

It can be done, but when consuming the data in Excel, via an OData API from an SQL database there are multiple points where the conversion logic can be implemented, therefor multiple plausible solutions.

Don't forget, you could apply this time zone logic as a transformation step inside Excel after the data has been retrieved. I hope this post inspires you to research a bit further and to choose a specific pathway. Then if you get stuck, please post a more focused question that details your specific attempt. We are here to help ;)