Data type datetimeoffset fails with invalid input syntax for type timestamp unless I force IS-8601 dates

[hyperman2]

Issue report

tds_fdw does not work on all mssql date/time types.

It crashes on datetimeoffset(3) but works on datetime

Workaround is specifying the ISO 8601 format

Operating system

Ubuntu 22.04 LTS, default install of postgres and tds_fdw

Version of tds_fdw

tds_fdw | 2.0.2 | public | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)

Version of PostgreSQL

From a psql session, paste the output of running SELECT version();

PostgreSQL 14.4 (Ubuntu 14.4-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit

Version of FreeTDS

$ dpkg -l|grep freetds ii freetds-common 1.3.6-1 all configuration files for FreeTDS SQL client libraries ii freetds-dev 1.3.6-1 amd64 MS SQL and Sybase client library (static libs and headers)

Logs

(sorry can't provide these, but the reproducers works on multiple dbs)

Sentences, data structures, data

CREATE FOREIGN TABLE thisfails(datetime timestamp without time zone NULL) SERVER anysqlserver OPTIONS (query 'SELECT CONVERT(datetimeoffset(3),GETDATE()) as datetime');

-- SELECT * FROM thisfails; ERROR: invalid input syntax for type timestamp: "Aug 19 2022 2:06:54:803PM"

-- Create a default date format cfr ISO-8601 $ cat /etc/freetds/locales.conf [default] date format = %FT%T.%z

-- Now it works SELECT * FROM thisfails;