Incorrect offset with datetimeoffset type in SQL Server

[penttin5]

When inserting/updating a Javascript Date value to a column of type datetimeoffset in SQL Server, the offset is always +00:00, which leads to wrong result if useUTC=false and process.env.TZ !== 'UTC'.

input	useUTC	app's TZ	expected	actual	correct?
new Date('2024-01-01T06:00:00Z')	true	'Z'	2024-01-01 06:00:00 +00:00	2024-01-01 06:00:00 +00:00	Y
new Date('2024-01-01T06:00:00Z')	true	'Europe/Helsinki'	2024-01-01 06:00:00 +00:00	2024-01-01 06:00:00 +00:00	Y
new Date('2024-01-01T06:00:00Z')	false	'Z'	2024-01-01 06:00:00 +00:00	2024-01-01 06:00:00 +00:00	Y
new Date('2024-01-01T06:00:00Z')	false	'Europe/Helsinki'	2024-01-01 08:00:00 +02:00	2024-01-01 08:00:00 +00:00	N

Changing the TediousDataType for Date values from #tedious.TYPES.DateTime to #tedious.TYPES.DateTimeOffset in MssqlDriver.#getTediousDataType seems to fix this:

  #getTediousDataType(value: unknown): any {
    ...
    if (isDate(value)) {
-      return this.#tedious.TYPES.DateTime
+      return this.#tedious.TYPES.DateTimeOffset
    }

I don't know enough about Tedious to know what the correct way to fix this is. My guess is that the correct TediousType should be read from the database metadata.

[igalklebanov]

Hey 👋

Related to #1164. Thanks for raising this!

Check this possible workaround @ #1161 (comment).