CODEWITHSUNDEEP
sqlsql-serverdateutc
Henkiee20
Henkiee20

Reputation: 87

Convert date column to new UTC date column

I wrote the following query on a SQL Server DB to convert a datetime column to a UTC date column.

select datetime
       , dateadd(minute,-datepart(tz,datetime),datetime) datetime_dt_utc
from table1

But I get the same same datetime for both columns.

What do I have to change to make it work?

Upvotes: 1

Views: 2448

Answers (1)

VBoka
VBoka

Reputation: 9083

Please try this:

DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), datetime)
  • The GETDATE() will return the current time
  • The GETUTCDATE() will retunr the current UTC time
  • The DATEDIFF function will calculate the difference between this two datetimes in hours
  • The DATEADD function will add this hours to your current datetime.

Another way is via usign the AT TIME ZONE

SELECT datetime AT TIME ZONE 'UTC' from table1

You can check your time zone with:

select CURRENT_TIMEZONE ( )

And use this:

SELECT datetime  AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'UTC' 
from table1

If you are in 'Pacific Standard Time' timezone

Upvotes: 2

Related Questions