I have an API that receives datetimeoffsets, and stored in a SQL Server in a DateTimeOffset column. However, some of the users of the API do not have the timezone information.

In that case, the system treat this like it is UTC.

I there any clever way to fool the SQL Server into having the timezone information optional/nullable?

In that case I could transform it to localtime if there was timezone information, or just show the datetime unmodified if no timezone information was present.

CodePudding user response：

You cannot remove the offset from datetimeoffset datatype. Some workarounds:

• Use datetimeoffset and datetime2 columns and populate one or the other depending on whether the input contains timezone information or not.

• Use datetime2 to store the datetime part of the input and varchar(6) to store the timezone offset, if present.

• Use at time zone to convert the input to a specific timezone and store the result as datetimeoffset. Here is an example:

select cast('2022-01-01 12:00:00 -05:00' as datetimeoffset);                          -- 2022-01-01 12:00:00 -05:00
select cast('2022-06-01 12:00:00 -04:00' as datetimeoffset);                          -- 2022-06-01 12:00:00 -04:00
select cast('2022-01-01 12:00:00' as datetime2) at time zone 'Eastern Standard Time'; -- 2022-01-01 12:00:00 -05:00
select cast('2022-06-01 12:00:00' as datetime2) at time zone 'Eastern Standard Time'; -- 2022-06-01 12:00:00 -04:00

CodePudding user response：

The conclusion is: No, it is not possible.

You have to find another way to solve the problem.