How to convert datetime fromUTC to local time

Question

I apologize if this is super easy, I'm still new to SQL and learning everyday. What I'm trying to do is query

SELECT ProjectName,
       RequestNumber,
       ContactId,
       ContactFirstName,
       ContactLastName,
       RequestReceivedDate,
       RequestCloseDate,
       Category,
       SubCategory,
       AssignedResourceId,
       OwningResourceId,
       RequestSource,
       ClosedImmediatelyFlag,
       RequestStatusCode
FROM dbo.system_requests;

BUT my RequestReceivedDate and RequestCloseDate, are off by 5 hours. I can't create a function as I'm working with a vendor database so looking to convert UTC to local time in my select statement and just not sure the best way of doing this?

Thoughts?

SQL is used everywhere in any RDBMS system, such as Oracle, SQL Server, DB2, Sybase, etc. So you are not answering my question. — jyao, Oct 25 '18 at 16:24
Hey sorry, again super new and still very much learning so my apologizes but it's SQL Server 2017 — Lyndi Andersen, Oct 25 '18 at 17:10
You may check this link: [Convert Datetime column from UTC to local time in select statement](https://stackoverflow.com/questions/8038744/convert-datetime-column-from-utc-to-local-time-in-select-statement) — jyao, Oct 25 '18 at 17:13

score 0 · Answer 1 · answered Oct 25 '18 at 19:23

SQL Server 2016 has support for time zones with the AT TIME ZONE 'UTC' statement. You can do this:

SELECT ProjectName,
       RequestNumber,
       ContactId,
       ContactFirstName,
       ContactLastName,
       RequestReceivedDate AT TIME ZONE 'UTC',
       RequestCloseDate AT TIME ZONE 'UTC',
       Category,
       SubCategory,
       AssignedResourceId,
       OwningResourceId,
       RequestSource,
       ClosedImmediatelyFlag,
       RequestStatusCode
FROM dbo.system_requests;

You can search for "AT TIME ZONE" on Google.

score 0 · Answer 2 · answered Oct 31 '18 at 18:32

So I ended up doing this and it worked and gave me what i wanted:

           SELECT CONVERT(
                             DATETIME,
                             SWITCHOFFSET(
                                             CONVERT(DATETIMEOFFSET, system_requests.RequestReceivedDate),
                                             DATENAME(TzOffset, SYSDATETIMEOFFSET())
                                         )
                         ) AS RequestReceivedDate
       ),
       (
           SELECT CONVERT(
                             DATETIME,
                             SWITCHOFFSET(
                                             CONVERT(DATETIMEOFFSET, system_requests.RequestCloseDate),
                                             DATENAME(TzOffset, SYSDATETIMEOFFSET())
                                       )
                         ) AS RequestClosedDate

How to convert datetime fromUTC to local time

2 Answers2