mssql: added support for TIME type (simulated via DATETIME col) [ticket:679]

3 files changed, 33 insertions, 1 deletions

diff --git a/CHANGES b/CHANGES
index 41231af7d..c0575f5cc 100644
--- a/CHANGES
+++ b/CHANGES
@@ -19,6 +19,9 @@
 - postgres
     - fixed max identifier length (63) [ticket:571]
 
+- mssql
+    - added support for TIME columns (simulated using DATETIME) [ticket:679]
+
 0.3.9
 - general
     - better error message for NoSuchColumnError [ticket:607]
diff --git a/lib/sqlalchemy/databases/mssql.py b/lib/sqlalchemy/databases/mssql.py
index 6b1b41123..81536aab1 100644
--- a/lib/sqlalchemy/databases/mssql.py
+++ b/lib/sqlalchemy/databases/mssql.py
@@ -103,6 +103,29 @@ class MSDate(sqltypes.Date):
     def get_col_spec(self):
         return "SMALLDATETIME"
 
+class MSTime(sqltypes.Time):
+    __zero_date = datetime.date(1900, 1, 1)
+
+    def __init__(self, *a, **kw):
+        super(MSTime, self).__init__(False)
+    
+    def get_col_spec(self):
+        return "DATETIME"
+
+    def convert_bind_param(self, value, dialect):
+        if isinstance(value, datetime.datetime):
+            value = datetime.datetime.combine(self.__zero_date, value.time())
+        elif isinstance(value, datetime.time):
+            value = datetime.datetime.combine(self.__zero_date, value)
+        return value
+
+    def convert_result_value(self, value, dialect):
+        if isinstance(value, datetime.datetime):
+            return value.time()
+        elif isinstance(value, datetime.date):
+            return datetime.time(0, 0, 0)
+        return value
+
 class MSDateTime_adodbapi(MSDateTime):
     def convert_result_value(self, value, dialect):
         # adodbapi will return datetimes with empty time values as datetime.date() objects.
@@ -310,6 +333,7 @@ class MSSQLDialect(ansisql.ANSIDialect):
         sqltypes.Float : MSFloat,
         sqltypes.DateTime : MSDateTime,
         sqltypes.Date : MSDate,
+        sqltypes.Time : MSTime,
         sqltypes.String : MSString,
         sqltypes.Binary : MSBinary,
         sqltypes.Boolean : MSBoolean,
@@ -914,3 +938,4 @@ dialect = MSSQLDialect
 
 
 
+
diff --git a/test/sql/testtypes.py b/test/sql/testtypes.py
index d858f748a..ed9de0912 100644
--- a/test/sql/testtypes.py
+++ b/test/sql/testtypes.py
@@ -290,7 +290,7 @@ class DateTest(AssertMixin):
 
             collist = [Column('user_id', INT, primary_key = True), Column('user_name', VARCHAR(20)), Column('user_datetime', DateTime),
                Column('user_date', Date), Column('user_time', TIMESTAMP)]
-        elif db.engine.name == 'mysql' or db.engine.name == 'mssql':
+        elif db.engine.name == 'mysql':
             # these dont really support the TIME type at all
             insert_data =  [
                  [7, 'jack', datetime.datetime(2005, 11, 10, 0, 0), datetime.datetime(2005, 11, 10, 0, 0, 0)],
@@ -311,6 +311,10 @@ class DateTest(AssertMixin):
                     [10, 'colber', None, None, None]
             ]
 
+            if db.engine.name == 'mssql':
+                # MSSQL Datetime values have only a 3.33 milliseconds precision
+                insert_data[2] = [9, 'foo', datetime.datetime(2005, 11, 10, 11, 52, 35, 547000), datetime.date(1970,4,1), datetime.time(23,59,59,997000)]
+
             fnames = ['user_id', 'user_name', 'user_datetime', 'user_date', 'user_time']
 
             collist = [Column('user_id', INT, primary_key = True), Column('user_name', VARCHAR(20)), Column('user_datetime', DateTime(timezone=False)),