Can't work with DATETIME under freebsd

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Install FreeTDS (latest version, tried few old).
2. Install pymssql (latest)
3. Tried simple query "select take_off_time from search_results where search_id 
= 1"

What is the expected output? What do you see instead?
Expected output: 1990-01-01 00:00
Instead:
Traceback (most recent call last):
  File "<stdin>", line 6, in <module>
  File "pymssql.pyx", line 464, in pymssql.Cursor.fetchall (pymssql.c:5846)
    rows = [row for row in self._source._conn]
  File "_mssql.pyx", line 336, in _mssql.MSSQLRowIterator.__next__ (_mssql.c:3118)
    return self.conn.fetch_next_row_dict(1)
  File "_mssql.pyx", line 929, in _mssql.MSSQLConnection.fetch_next_row_dict (_mssql.c:8834)
    row = self.fetch_next_row(throw)
  File "_mssql.pyx", line 920, in _mssql.MSSQLConnection.fetch_next_row (_mssql.c:8724)
    return self.get_row(rtc)
  File "_mssql.pyx", line 1077, in _mssql.MSSQLConnection.get_row (_mssql.c:10012)
    record += (self.convert_db_value(data, col_type, len),)
  File "_mssql.pyx", line 651, in _mssql.MSSQLConnection.convert_db_value (_mssql.c:6014)
    return datetime.datetime(di.year, di.month, di.day,
ValueError: microsecond must be in 0..999999

What version of the product are you using? On what operating system?
FreeBSD 8.2 (amd64)

I've tried to debug pymssql, added some debug code to track values of misc 
variables.
In the file _mssql.pyx I've added some debug output. 
Around line 644 (where I have following code):
dbdatecrack(self.dbproc, &di, <DBDATETIME *>data)

After calling this function in the "di" variable I have:

{'millisecond': 10095794, 'hour': 0, 'tzone': 8, 'month': 1, 'second': 0, 
'dayofyear': 2, 'weekday': 0, 'year': 1990, 'day': 1, 'minute': 0}

Original issue reported on code.google.com by solsh...@gmail.com on 29 Nov 2011 at 4:09

[GoogleCodeExporter]

Forgot to menition - 100% same setup works fine under Gentoo Linux without any 
issues.
And one more comment: changing DATATIME to DATETIME2 works fine, but I need 
DATETIME unfortunately.

Original comment by solsh...@gmail.com on 29 Nov 2011 at 4:13

[GoogleCodeExporter]

I'm not sure its a BSD thing.  I get the following on ubuntu:

rsyring@antec:~$ python 
Python 2.6.5 (r265:79063, Apr 16 2010, 13:57:41) 
[GCC 4.4.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import datetime as dt
>>> dt.datetime(year=1990, month=1, day=1, microsecond=10095794)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ValueError: microsecond must be in 0..999999

The 10095794 value for microsecond comes from the debug output you gave.  It 
sounds like the real issue is that MSSQL is returning more precision for 
microsecond than python datetime can handle.  

Can you produce a minimal test script that will reproduce?

Original comment by rsyr...@gmail.com on 29 Nov 2011 at 7:20

[GoogleCodeExporter]

Well, actually I dunno what to reproduce. It's not something related to python 
I think. I'm testing with simple script 
import pymssql
conn = 
pymssql.connect(host="xxx",user="xxx",password="xxx",database="xxx",as_dict=True
)
cur = conn.cursor()
sql_query = "select take_off_time from search_results where search_id = 1"
cur.execute(sql_query)
rows = cur.fetchall()

And my guess - something wrong with line dbdatecrack(self.dbproc, &di, 
<DBDATETIME *>data)

As result of this line in the variable di we have wrong value. F.e. with other 
queries I got error 'month should be in range 1..12'. In fact my debug code 
recorder 
...'month': 28,...

SO it's not about MSSQL returning something wrong. Keep in mind running this 
query with tsql I'm getting correct results. 

Original comment by solsh...@gmail.com on 29 Nov 2011 at 8:42

[GoogleCodeExporter]

You won't get the error in TSQL b/c the error comes when the python datetime 
object is instantiated.  

Your script is good so far.  Now, before you select, run the SQL to create a 
minimal table and populate it with one row of data that will result in the 
error you are seeing.

Original comment by rsyr...@gmail.com on 30 Nov 2011 at 1:39

[GoogleCodeExporter]

This is my simple testcase:

import pymssql

conn = 
pymssql.connect(host="xxx",user="xxx",password="xxx",database="xxx",as_dict=True
)
cur = conn.cursor()
cur.execute("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
OBJECT_ID(N'[dbo].[test_table]') AND type in (N'U')) DROP TABLE 
[dbo].[test_table]")

cur.execute("CREATE TABLE [dbo].[test_table]([date] [datetime] NOT NULL) ON 
[PRIMARY]")

cur.execute("insert into [dbo].[test_table](date) values('2011-11-30')")

cur.execute("select * from [dbo].[test_table]")

date = cur.fetchall()
conn.commit()

Error:
Traceback (most recent call last):
  File "test_skript.py", line 13, in <module>
    date = cur.fetchall()
  File "pymssql.pyx", line 464, in pymssql.Cursor.fetchall (pymssql.c:5846)
    rows = [row for row in self._source._conn]
  File "_mssql.pyx", line 336, in _mssql.MSSQLRowIterator.__next__ (_mssql.c:3086)
    return self.conn.fetch_next_row_dict(1)
  File "_mssql.pyx", line 922, in _mssql.MSSQLConnection.fetch_next_row_dict (_mssql.c:8658)
    row = self.fetch_next_row(throw)
  File "_mssql.pyx", line 913, in _mssql.MSSQLConnection.fetch_next_row (_mssql.c:8548)
    return self.get_row(rtc)
  File "_mssql.pyx", line 1070, in _mssql.MSSQLConnection.get_row (_mssql.c:9836)
    record += (self.convert_db_value(data, col_type, len),)
  File "_mssql.pyx", line 644, in _mssql.MSSQLConnection.convert_db_value (_mssql.c:5840)
    return datetime.datetime(di.year, di.month, di.day,
ValueError: month must be in 1..12

Value of variable "di" for this case:

{'millisecond': 10095794, 'hour': 0, 'tzone': 8, 'month': 30, 'second': 0, 
'dayofyear': 4, 'weekday': 0, 'year': 2011, 'day': 334, 'minute': 0}

According "You won't get the error in TSQL b/c the error comes when the python 
datetime object is instantiated." - I agree, but I feel problem not in the 
datatime.datetime call directly. Problen in parameter (di actually) with wrong 
values.

Original comment by solsh...@wemaketraffic.com on 30 Nov 2011 at 9:32

[GoogleCodeExporter]

thats interesting.  Just to clarify 1) you are using pymssql from HG tip and 2) 
that script fails on FreeBSD but works on linux.  Is that correct?

Original comment by rsyr...@gmail.com on 30 Nov 2011 at 1:59

• Changed state: Accepted

[GoogleCodeExporter]

1 - yes
2 - yes

Original comment by solsh...@wemaketraffic.com on 30 Nov 2011 at 2:00

[GoogleCodeExporter]

I am seeing this issue on Centos Linux using freetds 0.63. I found by upgrading 
freetds to 0.91 the problem went away.

Has anyone found any other fixes for this issue?

Original comment by doo...@gmail.com on 30 Dec 2011 at 5:17

[GoogleCodeExporter]

Hmmm, sounds like a FreeTDS issue if upgrading made it go away. Should this be 
closed?

Original comment by msabr...@gmail.com on 7 Jan 2013 at 6:38

[GoogleCodeExporter]

Works for me on OS X (which is FreeBSD-like) with pymssql hg tip (e99fb4f037be) 
and FreeTDS v0.92.dev.20130718:

marca@marca-mac:~/dev/hg-repos/pymssql$ cat issue_75.py import pymssql

conn = pymssql.connect(host="xxx",user="xxx",password="xxx",database="xxx",as_dict=True ) cur = conn.cursor() cur.execute("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test_table]') AND type in (N'U')) DROP TABLE [dbo].[test_table]")

cur.execute("CREATE TABLE [dbo].[test_table]([date] [datetime] NOT NULL) ON [PRIMARY]")

cur.execute("insert into [dbo].test_table values('2011-11-30')")

cur.execute("select * from [dbo].[test_table]")

date = cur.fetchall() print('date = %r' % date) conn.commit()

marca@marca-mac:~/dev/hg-repos/pymssql$ python2.6 issue_75.py date = [{'date': datetime.datetime(2011, 11, 30, 0, 0)}]

Original comment by msabr...@gmail.com on 7 Aug 2013 at 2:56

[GoogleCodeExporter]

Original comment by msabr...@gmail.com on 7 Aug 2013 at 3:00

• Added labels: FreeBSD

[GoogleCodeExporter]

Closing this and contacting OP (solshark). If OP reports that he is still 
having problem, then we can reopen it.

Original comment by msabr...@gmail.com on 7 Aug 2013 at 4:39

• Changed state: Invalid

[GoogleCodeExporter]

OP says: "Well, I switched to ubuntu and latest FreeTDS - have no issues with 
it."

Original comment by msabr...@gmail.com on 7 Aug 2013 at 4:45

[GoogleCodeExporter]

For anyone who finds this because they're having problems with pymssql and 
incorrect dates, keep in mind that FreeTDS must be built with MSDBLIB 
compatibility. See http://pymssql.sourceforge.net/freetds_dates.php for details.

Original comment by msabr...@gmail.com on 7 Aug 2013 at 10:01