tds-fdw / tds_fdw

A PostgreSQL foreign data wrapper to connect to TDS databases (Sybase and Microsoft SQL Server)
Other
380 stars 102 forks source link

Unable to get date earlier than 0100-01-01 #306

Open alewmt opened 2 years ago

alewmt commented 2 years ago

Issue report

Operating system

Sql Server 2019 Windows Server 2022 Postgres 13.5 debian “bullseye”

Version of tds_fdw

                                            List of installed extensions
  Name   | Version |   Schema   |                                    Description
---------+---------+------------+-----------------------------------------------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 tds_fdw | 2.0.2   | public     | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)
(2 rows)

Version of PostgreSQL

 PostgreSQL 13.5 (Debian 13.5-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

Version of FreeTDS

root@30e51d6614ba:/# dpkg -l|grep freetds
ii  freetds-common             1.2.3-1                      all          configuration files for FreeTDS SQL client libraries
ii  freetds-dev                1.2.3-1                      amd64        MS SQL and Sybase client library (static libs and headers)

MS SQL table

create table dbo.test (col1 date not null)
insert into dbo.test(col1) values ('0001-01-01')
insert into dbo.test(col1) values ('0099-01-01')

Postgres

CREATE SERVER mssql FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername '192.168.2.20', port '1433', database 'test', tds_version '7.4', msg_handler 'notice');
CREATE FOREIGN TABLE fdw.test (
    col1 date NOT NULL
) SERVER mssql OPTIONS (table_name 'dbo.test', row_estimate_method 'showplan_all');

/etc/freetds/locales.conf

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

select * from fdw.test returns

col1      |
----------+
2001-01-01|
1999-01-01|
SocSoftSpb commented 3 months ago

Same problem.