SELECT statement to fix a date/timezone problem in original data.

Question

An application creates a date in a SQL table that is actually a local timezone, but is stored as UTC in SQL. I cannot change the application to fix this issue. However, I am making a VIEW, and I would like to correct the UTC date into true UTC. However, I'm not finding the way to do this. I would like to do it in the SELECT section, perhaps with CASE or CONVERT. But, not sure how to do it. I would like suggestion, please.

dbo.table.date is recorded in UTC, but is actually CST.

SELECT dbo.table.date, dbo.table.Order

I was hoping to do something with this:

SELECT CASE(dbo.table.date AS XXXXXX) AS Date2, dbo.table.Order

I'm not sure what to write in the XXXX.

Thank you!

Answer 1

Use dateadd function.
Select dateadd(hour,-5,theWrongUTCDate)

Adjust the -5 to fit your need if this is not correct.

Answer 2

What do you mean by ‘recorded in UTC’?

Evaluate the next approach too:

declare @sample_date as datetime = '2020-08-24 14:30:00'
declare @utc_date as datetime = @sample_date AT TIME ZONE 'Central Standard Time' AT TIME ZONE 'UTC'
select @sample_date, @utc_date

Answer 3

Hi @Rod Martin ，
Please refer to conversion code between local time and UTC time:

DECLARE @LocalDate DATETIME,  
@UTCDate DATETIME,  
@LocalDate2 DATETIME  
   
SET @LocalDate = GETDATE()  
SET @UTCDate = DATEADD(hour, DATEDIFF(hour,GETDATE(),GETUTCDATE()), @LocalDate)  
SET @LocalDate2 = DATEADD(hour, DATEDIFF(hour,GETUTCDATE(),GETDATE()), @UTCDate)  
   
SELECT '1. Now' [Now], @LocalDate [DateTime]  
UNION  
SELECT '2. UTC', @UTCDate  
UNION  
SELECT '3. Now again', @LocalDate2  

If this doesn't solve your problem, please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo