How can I add/subtract the offset value into datetime and return new datetime value in SQL?-CodePudding

I am new to SQL and I want to add/subtract the offset value into the DateTime and return new date.

Currently, I am using this

SELECT 
    GETUTCDATE() AS UTCDate, 
    GETUTCDATE() AT TIME ZONE 'Eastern Standard Time'

which returns these values:

Current Result : 2021-12-28 07:19:39.320 -05:00

Expected Result : 2021-12-28 02:19:39.320

How can I achieve this? Any help would be appreciated. Thanks

CodePudding user response：

You can use DATEADD and DATEDIFF function:

SELECT GETUTCDATE() AS UTCDate, GETUTCDATE() AT TIME ZONE 'Eastern 
Standard Time' ,
 DATEADD(MINUTE, DATEDIFF(MINUTE ,GETUTCDATE(), GETUTCDATE() AT TIME ZONE 
'Eastern Standard Time')*-1, GETUTCDATE())

CodePudding user response：

GETUTCDATE() returns datetime type. You can use SYSDATETIMEOFFSET() instead to get datetimeoffset and convert it to a desired timezone.

SELECT 
    SYSDATETIMEOFFSET() AS UTCDate_with_timezone, 
    SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time';

When AT TIME ZONE operator is applied to datetime value it does not change the value, you just inform the system what is the timezone. And this timezone will be just appended to the value.