CODEWITHSUNDEEP
sql-serverexceltime
Gerben
Gerben

Reputation: 68

How to display local time in Excel when it is stored in UTC in SQL?

I'm trying to show a time in Excel that is part of a view in SQL Server. In SQL Server, it is a datetime2 column storing dates in UTC. The result should be a time in W. Europe Standard Time.

For example, when the SQL Server value is 2019-07-02 09:44:00, Excel should show 11:44.

The view I'm working on is used only for displaying the data in Excel to provide a detailed report on start/end times of employees and machines. I either have to provide the correct data from the view or configure the Excel sheet to do the translation. Both are acceptable, although it seems cleaner to do the translation in Excel.

I have tried using the datetime2 column value as-is, but I have also tried to convert the datetime2 to a datetimeoffset in my view using AT TIME ZONE. I can set the cell formatting to Time and even specify a location. However, this is not enough to translate the time. The result of the conversion is 

2019-07-02 09:44:00.0000000 +02:00

In Excel, the result of both methods is 09:44:00.

I have also tried to pre-format the date in my view. While the result of AT TIME ZONE contains all the information needed, I failed to find a way to add the 2 hours to the time and format it in "hh:mm". Is there a way to do this?

My query so far is quite basic for this field:

SELECT TOP 100 
    dbo.tasks.EmployeeStart AT TIME ZONE 'W. Europe Standard Time' AS 'Start Employee'

This is connected to Excel by using the Data > retrieve data options in the ribbon.

Upvotes: 1

Views: 550

Answers (2)

Fredrik Stigsson
Fredrik Stigsson

Reputation: 423

You can test this if your SQL Version is >= 2016.

SELECT TOP 100 (dbo.tasks.EmployeeStart AT TIME ZONE 'UTC') AT TIME ZONE 'W. Europe Standard Time' AS 'Start Employee';

Upvotes: 0

Marc Guillot
Marc Guillot

Reputation: 6465

Use the CONVERT function to convert from DatetimeOffset (the result of AT TIME ZONE) back to datetime.

select top 100
       Convert(datetime, current_timestamp at time zone 'UTC' at time zone 'W. Europe Standard Time', 0) 'Start Employee'

Upvotes: 1

Related Questions