I have code in postgresql transform to sql server In postgresql while inserting in table with data type timestamp with time zone in UTC format, it inserted with time zone

create table public.testt123 (tz timestamp with time zone)


insert into public.testt123
select now() at time zone 'utc'

select * from public.testt123

enter image description here

I have tried same with Sql server, below query

create table Test1(tz [datetimeoffset](7))

insert into Test1
select GETUTCDATE() AT TIME ZONE 'UTC'

enter image description here

It inserted without time zone, I have check using SYSDATETIMEOFFSET() but it gives time zone with current datetime not UTC

I have tried by left function, but it is correct way?

Select cast(left(SYSDATETIMEOFFSET() AT TIME ZONE 'UTC',28)   DATENAME(TZOFFSET, SYSDATETIMEOFFSET()) as [datetimeoffset](7))

enter image description here

CodePudding user response：

Based on the comments, I suspect what you want is:

SELECT SYSUTCDATETIME() AT TIME ZONE 'UTC' AT TIME ZONE 'India Standard Time';

Though this could be abbreviated to:

SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'India Standard Time';

CodePudding user response：

I have check using SYSDATETIMEOFFSET() but it gives time zone with current datetime not UTC

Correct, SYSDATETIMEOFFSET() returns a datetimeoffset but with the current UTC offset of the database server. Specify AT TIME ZONE 'UTC' to get a datetimeoffset with the UTC time with a zero offset:

SYSDATETIMEOFFSET() AT TIME ZONE 'UTC'