MSsql smalldattime truncates to only date

[Former user]

I found that when using pyodbc, MS SQLserver smalldatetime columns were always returned as a datetime object with only the date components set, and the time missing.

I've attached a one-liner patch to make the MSSQLDialect use MSDateTime_pyodbc instead of MSDate_pyodbc for smalldatetime columns.

[Michael Trier]

Although this will work, we really need to cleanup the dates in mssql. It's a mess. I will focus on this the next few days. Thanks for highlighting the problem.

[Former user]

(original author: ram) Note that the original intent of returning smalldatetime objects as datetime.date() is not an accident: they were meant to simulate a DATE type and hence the explicit truncation.

It would be fine to add a smalldatetime type to the MSSQL set of types; it could exist alongside the psuedo-DATE type, and if somebody wanted the crippled smalldatetime and could live with its limitations they could use SMALLDATETIME instead of DATE.

But I don't think it's a great idea to nuke the useful simulated DATE type in favor of pedantically matching the exact set of types supported by MSSQL.

[Mike Bayer]

lets make the names better and add documentation for "pseudo" types.

[Former user]

(original author: ram) A couple of FYI notes:

• SmallDate was added for ticket:884, which also truncates to date.
• MSSQL2008 adds new DATE and TIME types.

I would propose the following (new) mappings (t)=truncation:

SQLA MSSQL2008 MSSQL2000/5 datetime datetime datetime smalldatetime smalldatetime smalldatetime date date datetime (t) time time datetime (t) smalldate smalldate(t) smalldate (t) smalltime smalldate(t) smalldate (t)

[Former user]

(original author: ram) Let's try that table again:

||SQLA||MSSQL2008||MSSQL2000/5

||datetime||datetime||datetime ||smalldatetime||smalldatetime||smalldatetime ||date||date||datetime (t) ||time||time||datetime (t) ||smalldate||smalldate(t)||smalldate (t) ||smalltime|| smalldate(t)||smalldate (t)

[Michael Trier]

Looks good to me. My only question is with smalldate and smalltime. Why a smalldatetime(t) on MSSQL2008 instead of a date and time?

[Michael Trier]

Corrected in 5a6b8ef89aff0862a8fcbfcb53c4d79184565c3d and bc2c1b2f94a9815b4f18cc185eb3bc13c83063aa.

[Mike Bayer]

Removing milestone: 0.5.0 (automated comment)