Re: current_timezone was: ISO-8601 & Birgitta's Solution

On Tue, Feb 4, 2020 at 8:09 AM Rob Berendt <rob@xxxxxxxxx> wrote:

So, how would you adjust Birgitta's function to work on SQL Server?

Well, if you use their built-in timezone-aware data type
(datetimeoffset), then you just specify the proper cosmetic format
using their CONVERT function, which in this case is 126:

CONVERT(VARCHAR(33), mydatetimeoffset, 126)

The above is an expression which evaluates to a VARCHAR(33) value
representing mydatetimeoffset in ISO 8601 format.

I don't have a SQL Server instance handy to test anything. I'm just
going by what I could find on the Web. I believe 33 is a common
maximum length to use because that should accommodate the full
fractional-second precision afforded by modern SQL Server data types
(7 digits past the decimal point, or tenths of a microsecond).

If your data is not timezone-aware, then the best you can do *easily*
is to find out what the current UTC offset is and slap that onto the
end of your data. (The same format 126 applied to timezone-unaware
data will generate ISO 8601 but without the offset at the end.) If I
understand correctly, that is what Birgitta is doing. In SQL Server,
the most straightforward way that I've found for getting the current
UTC offset (as a string) is

DATENAME(tz, SYSDATETIMEOFFSET())

So, the complete expression in SQL Server for Birgitta's function (if
I understand correctly) seems to be

CONVERT(VARCHAR(33), mydatetime, 126) + DATENAME(tz, SYSDATETIMEOFFSET())

You could of course also use the CONCAT function instead of the "plus" operator.

Note that this is going to be wrong if mydatetime is during daylight
time but the system is currently in standard time, or vice versa. This
is why I emphasized *easily*. Doing it *right* is definitely not easy
without help from a third-party library. (You could sort of put
together something yourself for your locale, but if your timestamps go
back far enough, you would need the historical daylight time rules.)
Even with a proper timezone library, it is *impossible* to be sure
about the extra hour in the fall if the data going in wasn't stored
with some way to differentiate before versus after the end of DST.

John Y.