SQL Anywhere datetime column: invalid input syntax for type timestamp

[cstork]

Issue report

When I create a foreign table with IMPORT FOREIGN SCHEMA for a table in a SQL Anywhere DB there's a column of type datetime on the foreign server which is mapped to a local column of type timestamp without time zone.

Once I try to read the data (i.e. issue a SELECT * statement) I get the following error:

ERROR:  22007: invalid input syntax for type timestamp: "Mar 19 2020 02:26:21:971000PM"
LOCATION:  DateTimeParseError, datetime.c:3755

Any ideas?

Operating system

NAME="Ubuntu"
VERSION="20.04.1 LTS (Focal Fossa)"

Version of tds_fdw

tds_fdw version 2.0.2

From a psql session, paste the outputs of running \dx+

      Objects in extension "plpgsql"
            Object description
───────────────────────────────────────────
 function plpgsql_call_handler()
 function plpgsql_inline_handler(internal)
 function plpgsql_validator(oid)
 language plpgsql
(4 rows)

     Objects in extension "postgres_fdw"
             Object description
─────────────────────────────────────────────
 foreign-data wrapper postgres_fdw
 function postgres_fdw_handler()
 function postgres_fdw_validator(text[],oid)
(3 rows)

     Objects in extension "tds_fdw"
           Object description
────────────────────────────────────────
 foreign-data wrapper tds_fdw
 function tds_fdw_handler()
 function tds_fdw_validator(text[],oid)
(3 rows)

Version of PostgreSQL

PostgreSQL 13.0 (Ubuntu 13.0-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit

Version of FreeTDS

dpkg -l|grep freetds

ii  freetds-common                         1.1.6-1.1                         all          configuration files for FreeTDS SQL client libraries
ii  freetds-dev                            1.1.6-1.1                         amd64        MS SQL and Sybase client library (static libs and headers)

[Neopallium]

I had this problem too and found a solution.

The problem is caused by the default date format in freetds. The default format uses a ':' between the seconds and milliseconds, but postgresql expects a '.'

Create file /etc/freetds/locales.conf on the postgresql server to change the date format used by freetds:

[default]
        date format = %b %e %Y %I:%M:%S.%z%p

[okkof2000]

i meet the same trouble in win10, how to change ":" to "." in windows? i can not find the freetds.conf or locales.conf

[jef-sure]

I had very similar problem and solution from https://github.com/tds-fdw/tds_fdw/issues/271#issuecomment-731581415 really helped me. Thanks @Neopallium !