'Can you make SQL Server datetimeoffset "nullable"?

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.

sqlsql-servertsqldatetimeoffset


Solution 1:[1]

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

Solution 2:[2]

The conclusion is: No, it is not possible.

You have to find another way to solve the problem.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 barnonline

Related Questions