It looks like there is an invalid time value coming back from SQL server. When I define the columns as date build the definition as a query with cast statements ( I don't care about the time anyway ) it works

from tds_fdw.

It sounds like your installation of MS SQL Server or FreeTDS is using a colon (':') as the separator for milliseconds, but PostgreSQL will only accept a period ('.'):

postgres=# select 'Jan 21 2013'::date;
    date    
------------
 2013-01-21
(1 row)

postgres=# select 'Jan 21 2013 09:39:36'::timestamp;
      timestamp      
---------------------
 2013-01-21 09:39:36
(1 row)

postgres=# select 'Jan 21 2013 09:39:36AM'::timestamp;
      timestamp      
---------------------
 2013-01-21 09:39:36
(1 row)

postgres=# select 'Jan 21 2013 09:39:36:523AM'::timestamp;
ERROR:  invalid input syntax for type timestamp: "Jan 21 2013 09:39:36:523AM"
LINE 1: select 'Jan 21 2013 09:39:36:523AM'::timestamp;
               ^
postgres=# select 'Jan 21 2013 09:39:36.523AM'::timestamp;
        timestamp        
-------------------------
 2013-01-21 09:39:36.523
(1 row)

What version of MS SQL Server are you using? This behavior sounds quite different from that described by the datetime documentation. It doesn't look like SET DATEFORMAT could cause this either.

This could be some odd quirk of how FreeTDS treats timestamps.

Questions:

• What OS are you using?
• What version of FreeTDS?
• What versions of MS SQL Server?
• What is the contents of FreeTDS's locales.conf file?

As for your second question: I may be able to alter tds_fdw to suppress NOTICE messages unless a particular flag is set in the CREATE SERVER command, so the user only sees ERROR messages for that particular foreign server. Would that feature interest you?

from tds_fdw.

Geoff,
Thanks for your quick response. I would be interested in a flag to suppress those messages. I got around the timestamp issue by casting it as a varchar(50) and when I insert into the base table I convert it back to a timestamp. I also had the suggestion of casting it as smalldatetime so I will try that as well. The answer to your other questions are below:

OS version - Ubuntu 14.04.1 LTS (GNU/Linux 3.13.0-37-generic x86_64)
FreeTDS version - sudo apt-get install libsybdb5 freetds-dev freetds-common
MS SQL version - MS SQL 2012 but running in 2005 compatibility mode ( don't ask - it's why we are creating a new platform)
locales.conf file: /usr/share/doc/freetds-common/examples/locales.conf
[default]
date format = %b %e %Y %I:%M:%S:%z%p

[en_US]
date format = %b %e %Y %I:%M:%S:%z%p
language = us_english
charset = iso_1

[es_ES]
date format = %b %d %Y %I:%M%p
language = spanish
charset = iso_1

[pt_BR]
date format = %d/%m/%Y %H:%M
language = Portuguese
charset = iso_1

[it_IT]
date format = %d/%m/%Y %H:%M
language = Italiano
charset = iso_1

/etc/freetds/freetds.conf

$Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $

This file is installed by FreeTDS if no file by the same

name is found in the installation directory.

For information about the layout of this file and its settings,

see the freetds.conf manpage "man freetds.conf".

Global settings are overridden by those in a database

server specific section

[global]
# TDS protocol version
; tds version = 4.2

Whether to write a TDSDUMP file for diagnostic purposes

(setting this to /tmp is insecure on a multi-user system)

; dump file = /tmp/freetds.log
; debug flags = 0xffff

Command and connection timeouts

; timeout = 10
; connect timeout = 10

If you get out-of-memory errors, it may mean that your client

is trying to allocate a huge buffer for a TEXT field.

Try setting 'text size' to a more reasonable limit

text size = 64512

A typical Sybase server

[egServer50]
host = symachine.domain.comhttp://symachine.domain.com
port = 5000
tds version = 5.0

A typical Microsoft server

[egServer70]
host = ntmachine.domain.comhttp://ntmachine.domain.com
port = 1433
tds version = 7.0

Thanks
April Peesel
guest|METRICS
1602 Village Market Blvd., SE
Leesburg, VA 20175
Office: 703.297.3421 | Mobile: 703-587-6441
[email protected]:[email protected] | www.guestmetrics.comhttp://www.guestmetrics.com/

On Oct 28, 2014, at 7:25 PM, Geoff Montee <[email protected]mailto:[email protected]> wrote:

It sounds like your installation of MS SQL Server or FreeTDS is using a colon (':') as the separator for milliseconds, but PostgreSQL will only accept a period ('.'):

postgres=# select 'Jan 21 2013'::date;

date

2013-01-21
(1 row)

postgres=# select 'Jan 21 2013 09:39:36'::timestamp;

timestamp

2013-01-21 09:39:36
(1 row)

postgres=# select 'Jan 21 2013 09:39:36AM'::timestamp;

timestamp

2013-01-21 09:39:36
(1 row)

postgres=# select 'Jan 21 2013 09:39:36:523AM'::timestamp;
ERROR: invalid input syntax for type timestamp: "Jan 21 2013 09:39:36:523AM"
LINE 1: select 'Jan 21 2013 09:39:36:523AM'::timestamp;
^
postgres=# select 'Jan 21 2013 09:39:36.523AM'::timestamp;

timestamp

2013-01-21 09:39:36.523
(1 row)

What version of MS SQL Server are you using? This behavior sounds quite different from that described by the datetime documentationhttp://technet.microsoft.com/en-us/library/ms186724.aspx. It doesn't look like SET DATEFORMAThttp://technet.microsoft.com/en-us/library/ms189491.aspx could cause this either.

This could be some odd quirk of how FreeTDS treats timestampshttp://www.freetds.org/userguide/locales.htm.

Questions:

• What OS are you using?
• What version of FreeTDS?
• What versions of MS SQL Server?
• What is the contents of FreeTDS's locales.conf file?

As for your second question: I may be able to alter tds_fdw to suppress NOTICE messages unless a particular flag is set in the CREATE SERVER command, so the user only sees ERROR messages for that particular foreign server. Would that feature interest you?

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/13#issuecomment-60849457.

from tds_fdw.

The problem appears to be with FreeTDS's locales.conf. The default and en_US locales clearly separates the seconds and milliseconds with a colon (':'). This doesn't appear to be an Ubuntu problem. The locales.conf in their git repo also has these settings.

A "solution" should be to change this file to use a dot ('.') to separate the seconds and milliseconds:

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

[en_US]
date format = %b %e %Y %I:%M:%S.%z%p
language = us_english
charset = iso_1

Let me know if that works for you.

I wonder why they chose these defaults. I'm not aware of any standard (formal or informal) that specifies a colon separator in this scenario. Perhaps I'll post to the FreeTDS mailing list about this.

As for the change to the verbose NOTICE messages: I may be able to tweak that this weekend.

from tds_fdw.

Hi,

I've been following this thread and am having the same issue. I've tried Geoff's proposed fixed above and still have this error in my postgres logs:

  < 2015-01-08 13:14:29.311 EST >ERROR:  invalid input syntax for type timestamp: "Dec 24 2014 01:36:58:087PM"

I think (not sure) this could be a problem with the %z in the locales.conf?

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

[en_US]
        date format = %b %e %Y %I:%M:%S.%z%p
        language = us_english
        charset = iso_1

FreeTDS documentation states here:

For the most part, see you system documentation for strftime(3) (man 3 strftime). You will see there
though that strftime(3) has no provision for milliseconds. The locales.conf format string uses %z for
milliseconds.

man 3 strftime shows %z reserved for something other than milliseconds (link here)

%z

The +hhmm or -hhmm numeric timezone (that is, the hour and minute offset from UTC). (SU)

Maybe strftime recently added a %z that confuses something?

For my short term, Is there anyway to truncate the milliseconds (or anything) in locales.conf? I could do that too i think... would really like to get this working.

from tds_fdw.

Ok, apologies but i restarted my instance after the modification to locales.conf and all seems to work now.

Also, i noticed i missed something in the documentation for freetds:

If your system's strftime(3) does employ %z for its own use, it will not be given that chance by
FreeTDS. FreeTDS will consume the %z for its milliseconds needs, and will not pass it on to
strftime(3).

Thanks everyone for your help.

from tds_fdw.

I had to set the date format in locales.conf rather than freetds.conf

The locales.conf file seems to be deprecated, but the version of freetds I have ignored the date format setting in freetds.conf

from tds_fdw.

I posted about this on the FreeTDS mailing list. In particular, I asked if they would consider changing the default date format in the next release. I'll watch for a response.

http://lists.ibiblio.org/pipermail/freetds/2015q2/029275.html

from tds_fdw.

Thanks. The tds_fdw wrapper works great for us, really a nice capability to
have available.

On Thu, Apr 9, 2015 at 2:44 PM, Geoff Montee [email protected]
wrote:

I posted about this on the FreeTDS mailing list. In particular, I asked if
they would consider changing the default date format in the next release.
I'll watch for a response.

http://lists.ibiblio.org/pipermail/freetds/2015q2/029275.html

—
Reply to this email directly or view it on GitHub
#13 (comment).

from tds_fdw.

I hope to eventually remove the requirement to change locales.conf. See issue #28.

from tds_fdw.

Issue #28 has a fix out now.

from tds_fdw.

Fixed in v1.0.4.

from tds_fdw.

I use PostgreSQL 10 on CentOS 7 with freetds 1.1.6-0.el7, postgresql-10-tds_fdw 2.0.0-alpha.3.2.el7 from tds_fdw repository and I just had to change ":" to "." in /etc/locales.conf to get date from MSSQL 2008R2 "date" column instead of ERROR: invalid input syntax for type timestamp: .
Previous to this, I had to add

        tds version = 7.3
        client charset = UTF-8

to /etc/freetds.conf to get proper letters (Serbian_Latin collation).
If you need more data please tell me what you need.

from tds_fdw.

I thing this behaviour is back, at least in current debian jessie pakages.

from tds_fdw.

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

from tds_fdw.

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

Mine (on Windows VM) is in c:\Windows\System32\

If it is not there, you can search filesystem.

from tds_fdw.

thanks for your reply,
i used the postgresql12.6 for windows
I download the tds_fdw for windows from #53 (comment)
i just past all the files in the pg folder,
after IMPORT FOREIGN SCHEMA,i can see the data except the timestamp data,
it show that
ERROR: invalid input syntax for type timestamp: ""Jan 03 2020 08:43:00:000PM""
i have search all the filessystem ,can not found the freetds.conf or locales.conf
i create a locales.conf ,and paste it into
c:\Windows\System32
C:\Users\username\AppData\Roaming\freetds
but it is not work
what should i do next?
thanks again

from tds_fdw.

this still a problem related: #28
fix: #271
coment https://github.com/tds-fdw/tds_fdw/issues/271#issuecomment-731581415
(restart postgresql instance)

from tds_fdw.