mssql date / time refactor.

- Added new MSSmallDateTime, MSDateTime2, MSDateTimeOffset, MSTime types
- Refactored the Date/Time types. The smalldatetime data type no longer
  truncates to a date only, and will now be mapped to the MSSmallDateTime
  type. Closes #1254.

1 files changed, 91 insertions, 43 deletions

diff --git a/lib/sqlalchemy/databases/mssql.py b/lib/sqlalchemy/databases/mssql.py
index 15f30942a..35d167c91 100644
--- a/lib/sqlalchemy/databases/mssql.py
+++ b/lib/sqlalchemy/databases/mssql.py
@@ -209,6 +209,16 @@ render::
 If ``nullable`` is ``True`` or ``False`` then the column will be
 ``NULL` or ``NOT NULL`` respectively.
 
+Date / Time Handling
+--------------------
+For MSSQL versions that support the ``DATE`` and ``TIME`` types
+(MSSQL 2008+) the data type is used. For versions that do not
+support the ``DATE`` and ``TIME`` types a ``DATETIME`` type is used
+instead and the MSSQL dialect handles converting the results
+properly. This means ``Date()`` and ``Time()`` are fully supported
+on all versions of MSSQL. If you do not desire this behavior then
+do not use the ``Date()`` or ``Time()`` types.
+
 Compatibility Levels
 --------------------
 MSSQL supports the notion of setting compatibility levels at the
@@ -402,32 +412,77 @@ class MSSmallInteger(MSInteger):
         return "SMALLINT"
 
 
-class MSDateTime(sqltypes.DateTime):
-    def __init__(self, *a, **kw):
-        super(MSDateTime, self).__init__(False)
+class _DateTimeType(object):
+    """Base for MSSQL datetime types."""
 
+    def bind_processor(self, dialect):
+        # if we receive just a date we can manipulate it
+        # into a datetime since the db-api may not do this.
+        def process(value):
+            if type(value) is datetime.date:
+                return datetime.datetime(value.year, value.month, value.day)
+            return value
+        return process
+
+
+class MSDateTime(_DateTimeType, sqltypes.DateTime):
     def get_col_spec(self):
         return "DATETIME"
 
 
-class MSSmallDate(sqltypes.Date):
-    def __init__(self, *a, **kw):
-        super(MSSmallDate, self).__init__(False)
+class MSDate(sqltypes.Date):
+    def get_col_spec(self):
+        return "DATE"
+
+
+class MSTime(sqltypes.Time):
+    def __init__(self, precision=None, **kwargs):
+        self.precision = precision
+        super(MSTime, self).__init__()
 
     def get_col_spec(self):
+        if self.precision:
+            return "TIME(%s)" % self.precision
+        else:
+            return "TIME"
+
+
+class MSSmallDateTime(_DateTimeType, sqltypes.TypeEngine):
+    def get_col_spec(self):
         return "SMALLDATETIME"
 
-    def result_processor(self, dialect):
-        def process(value):
-            # If the DBAPI returns the value as datetime.datetime(), truncate it back to datetime.date()
-            if type(value) is datetime.datetime:
-                return value.date()
-            return value
-        return process
 
+class MSDateTime2(_DateTimeType, sqltypes.TypeEngine):
+    def __init__(self, precision=None, **kwargs):
+        self.precision = precision
 
-class MSDate(sqltypes.Date):
-    def __init__(self, *a, **kw):
+    def get_col_spec(self):
+        if self.precision:
+            return "DATETIME2(%s)" % self.precision
+        else:
+            return "DATETIME2"
+
+
+class MSDateTimeOffset(_DateTimeType, sqltypes.TypeEngine):
+    def __init__(self, precision=None, **kwargs):
+        self.precision = precision
+
+    def get_col_spec(self):
+        if self.precision:
+            return "DATETIMEOFFSET(%s)" % self.precision
+        else:
+            return "DATETIMEOFFSET"
+
+
+class MSDateTimeAsDate(_DateTimeType, MSDate):
+    """ This is an implementation of the Date type for versions of MSSQL that
+    do not support that specific type. In order to make it work a ``DATETIME``
+    column specification is used and the results get converted back to just
+    the date portion.
+
+    """
+
+    def __init__(self, *args, **kwargs):
         super(MSDate, self).__init__(False)
 
     def get_col_spec(self):
@@ -435,14 +490,22 @@ class MSDate(sqltypes.Date):
 
     def result_processor(self, dialect):
         def process(value):
-            # If the DBAPI returns the value as datetime.datetime(), truncate it back to datetime.date()
+            # If the DBAPI returns the value as datetime.datetime(), truncate
+            # it back to datetime.date()
             if type(value) is datetime.datetime:
                 return value.date()
             return value
         return process
 
 
-class MSTime(sqltypes.Time):
+class MSDateTimeAsTime(MSTime):
+    """ This is an implementation of the Time type for versions of MSSQL that
+    do not support that specific type. In order to make it work a ``DATETIME``
+    column specification is used and the results get converted back to just
+    the time portion.
+
+    """
+
     __zero_date = datetime.date(1900, 1, 1)
 
     def __init__(self, *a, **kw):
@@ -481,24 +544,6 @@ class MSDateTime_adodbapi(MSDateTime):
         return process
 
 
-class MSDateTime_pyodbc(MSDateTime):
-    def bind_processor(self, dialect):
-        def process(value):
-            if type(value) is datetime.date:
-                return datetime.datetime(value.year, value.month, value.day)
-            return value
-        return process
-
-
-class MSDate_pyodbc(MSDate):
-    def bind_processor(self, dialect):
-        def process(value):
-            if type(value) is datetime.date:
-                return datetime.datetime(value.year, value.month, value.day)
-            return value
-        return process
-
-
 class MSText(_StringType, sqltypes.Text):
     """MSSQL TEXT type, for variable-length text up to 2^31 characters."""
 
@@ -915,8 +960,11 @@ class MSSQLDialect(default.DefaultDialect):
         'numeric' : MSNumeric,
         'float' : MSFloat,
         'datetime' : MSDateTime,
+        'datetime2' : MSDateTime2,
+        'datetimeoffset' : MSDateTimeOffset,
         'date': MSDate,
-        'smalldatetime' : MSSmallDate,
+        'time': MSTime,
+        'smalldatetime' : MSSmallDateTime,
         'binary' : MSBinary,
         'varbinary' : MSVarBinary,
         'bit': MSBoolean,
@@ -1267,6 +1315,13 @@ class MSSQLDialect_pyodbc(MSSQLDialect):
     def __init__(self, description_encoding='latin-1', **params):
         super(MSSQLDialect_pyodbc, self).__init__(**params)
         self.description_encoding = description_encoding
+
+        self.colspecs = MSSQLDialect.colspecs.copy()
+        self.ischema_names = MSSQLDialect.ischema_names.copy()
+        if self.server_version_info < (10,):
+            self.colspecs[sqltypes.Date] = MSDateTimeAsDate
+            self.colspecs[sqltypes.Time] = MSDateTimeAsTime
+
         # FIXME: scope_identity sniff should look at server version, not the ODBC driver
         # whether use_scope_identity will work depends on the version of pyodbc
         try:
@@ -1280,13 +1335,6 @@ class MSSQLDialect_pyodbc(MSSQLDialect):
         import pyodbc as module
         return module
 
-    colspecs = MSSQLDialect.colspecs.copy()
-    colspecs[sqltypes.Date] = MSDate_pyodbc
-    colspecs[sqltypes.DateTime] = MSDateTime_pyodbc
-    ischema_names = MSSQLDialect.ischema_names.copy()
-    ischema_names['smalldatetime'] = MSDate_pyodbc
-    ischema_names['datetime'] = MSDateTime_pyodbc
-
     def make_connect_string(self, keys, query):
         if 'max_identifier_length' in keys:
             self.max_identifier_length = int(keys.pop('max_identifier_length'))