CODEWITHSUNDEEP
sqlsql-server
Devang Patel
Devang Patel

Reputation: 1843

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

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:

enter image description here

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

Upvotes: 1

Views: 1323

Answers (2)

Serg
Serg

Reputation: 22811

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.

Upvotes: 0

Lev Gelman
Lev Gelman

Reputation: 205

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())

Upvotes: 1

Related Questions