Problem with smalldatetime

[and-hom]

I've created foreign tables from remote MS SQL. I have a table with smalldatetime field:

SELECT COLUMN_NAME,DATA_TYPE from INFORMATION_SCHEMA.columns where table_name = 'Request' and column_name="RequestDate" order by ordinal_position;

DATA_TYPE                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------
smalldatetime       

In foreign table

\d+ myschema."Request"
........
RequestDate                          | timestamp(0) without time zone |           | (column_name 'RequestDate')                          | plain 

In MS SQL

1>> SELECT TOP 1 RequestDate FROM myschema.Request;
2>> go
RequestDate               
--------------------------
Dec 14 2011 09:01AM       

(1 rows affected)
1>> 

In Postgres

SELECT "RequestDate" FROM myschema."Request" LIMIT 1;
...

ERROR:  invalid input syntax for type timestamp: "Dec 14 2011 09:01:00:000AM"

[Smtgr14]

The error is here:

"Dec 14 2011 09:01:00:000AM"
                     ^

PostgreSQL does not like a colon separator for miliseconds. You will need to add a date format to /etc/freetds/locales.conf FreeTDS Locales.conf

I personally use

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

[GeoffMontee]

Another option would be to upgrade to PostgreSQL 9.4 or greater. With PostgreSQL 9.4 and up, tds_fdw no longer treats the datetime as a string. Instead, it breaks it apart and uses make_timestamp function to build the timestamp value. This function was added in PostgreSQL 9.4, which is why that is the minimum version supported for this.

[arenius]

I'm using PostgreSQL 9.5 with a tds_fdw that I got from git today, and I have this exact same problem. The locales.conf work around does not work for me.

[Smtgr14]

Is the error exactly the same? If not can you post the error a select statement generates?

[arenius]

Everything is exactly the same for me as it is for and-hom. I was going to leave a report that looked just like his, but he already had.

NOTICE:  tds_fdw: Getting results
ERROR:  invalid input syntax for type timestamp: "Dec 22 1991 12:00:00:000AM"

EDIT: Okay, so. I didn't have a locales.conf originally and when I first tried to make one with the given date format line I didn't add [default] to the file.

Once I made locales.conf look like:

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

Things began to work. Sorry for the noise and thank you for your help.

[Smtgr14]

Wonderful! Could you be a sweetie and close this issue? As long as everything is working, of course.

[tskenb]

Glad this is still open because I'm having the same problem, with smalldatetime only - not datetime or datetime2. My locales.conf is correct and tsql reads the column fine, so it doesn't appear to be a freetds problem even though it's the exact same problem that arenius saw and his was. Any other theories about what might cause this?