microsoft · bewithgaurav · Aug 5, 2025 · Jul 29, 2025 · Jul 29, 2025 · Jul 30, 2025
diff --git a/mssql_python/type.py b/mssql_python/type.py
@@ -9,50 +9,50 @@
 
 
 # Type Objects
-class STRING:
+class STRING(str):
     """
     This type object is used to describe columns in a database that are string-based (e.g. CHAR).
     """
 
-    def __init__(self) -> None:
-        self.type = "STRING"
+    def __new__(cls):
+        return str.__new__(cls, "")
 
 
-class BINARY:
+class BINARY(bytearray):
     """
     This type object is used to describe (long)
     binary columns in a database (e.g. LONG, RAW, BLOBs).
     """
 
-    def __init__(self) -> None:
-        self.type = "BINARY"
+    def __new__(cls):
+        return bytearray.__new__(cls)
 
 
-class NUMBER:
+class NUMBER(float):
     """
     This type object is used to describe numeric columns in a database.
     """
 
-    def __init__(self) -> None:
-        self.type = "NUMBER"
+    def __new__(cls):
+        return float.__new__(cls, 0.0)
 
 
-class DATETIME:
+class DATETIME(datetime.datetime):
     """
     This type object is used to describe date/time columns in a database.
     """
 
-    def __init__(self) -> None:
-        self.type = "DATETIME"
+    def __new__(cls, year: int = 1, month: int = 1, day: int = 1):
+        return datetime.datetime.__new__(cls, year, month, day)
 
 
-class ROWID:
+class ROWID(int):
     """
-    This type object is used to describe the “Row ID” column in a database.
+    This type object is used to describe the "Row ID" column in a database.
     """
 
-    def __init__(self) -> None:
-        self.type = "ROWID"
+    def __new__(cls):
+        return int.__new__(cls, 0)
 
 
 # Type Constructors
@@ -90,18 +90,44 @@ def TimeFromTicks(ticks: int) -> datetime.time:
     """
     Generates a time object from ticks.
     """
-    return datetime.time(*time.gmtime(ticks)[3:6])
+    return datetime.time(*time.localtime(ticks)[3:6])
 
 
 def TimestampFromTicks(ticks: int) -> datetime.datetime:
     """
     Generates a timestamp object from ticks.
     """
-    return datetime.datetime.fromtimestamp(ticks, datetime.timezone.utc)
-
-
-def Binary(string: str) -> bytes:
-    """
-    Converts a string to bytes using UTF-8 encoding.
-    """
-    return bytes(string, "utf-8")
+    return datetime.datetime.fromtimestamp(ticks)
+
+
+def Binary(value) -> bytes:
+    """
+    Converts a string or bytes to bytes for use with binary database columns.
+
+    This function follows the DB-API 2.0 specification and pyodbc compatibility.
+    It accepts only str and bytes/bytearray types to ensure type safety.
+
+    Args:
+        value: A string (str) or bytes-like object (bytes, bytearray)
+
+    Returns:
+        bytes: The input converted to bytes
+
+    Raises:
+        TypeError: If the input type is not supported
+
+    Examples:
+        Binary("hello")           # Returns b"hello"
+        Binary(b"hello")          # Returns b"hello"  
+        Binary(bytearray(b"hi"))  # Returns b"hi"
+    """
+    if isinstance(value, bytes):
+        return value
+    elif isinstance(value, bytearray):
+        return bytes(value)
+    elif isinstance(value, str):
+        return value.encode("utf-8")
+    else:
+        # Raise TypeError for unsupported types to improve type safety
+        raise TypeError(f"Cannot convert type {type(value).__name__} to bytes. "
+                       f"Binary() only accepts str, bytes, or bytearray objects.")
diff --git a/tests/test_002_types.py b/tests/test_002_types.py
@@ -1,21 +1,23 @@
 import pytest
 import datetime
+import time
 from mssql_python.type import STRING, BINARY, NUMBER, DATETIME, ROWID, Date, Time, Timestamp, DateFromTicks, TimeFromTicks, TimestampFromTicks, Binary
 
 def test_string_type():
-    assert STRING().type == "STRING", "STRING type mismatch"
+    assert STRING() == str(), "STRING type mismatch"
+
 
 def test_binary_type():
-    assert BINARY().type == "BINARY", "BINARY type mismatch"
+    assert BINARY() == bytearray(), "BINARY type mismatch"
 
 def test_number_type():
-    assert NUMBER().type == "NUMBER", "NUMBER type mismatch"
+    assert NUMBER() == float(), "NUMBER type mismatch"
 
 def test_datetime_type():
-    assert DATETIME().type == "DATETIME", "DATETIME type mismatch"
+    assert DATETIME(2025, 1, 1) == datetime.datetime(2025, 1, 1), "DATETIME type mismatch"
 
 def test_rowid_type():
-    assert ROWID().type == "ROWID", "ROWID type mismatch"
+    assert ROWID() == int(), "ROWID type mismatch"
 
 def test_date_constructor():
     date = Date(2023, 10, 5)
@@ -41,18 +43,18 @@ def test_date_from_ticks():
     assert date == datetime.date(2023, 10, 5), "DateFromTicks returned incorrect date"
 
 def test_time_from_ticks():
-    ticks = 1696500000  # Corresponds to 10:00:00
-    time = TimeFromTicks(ticks)
-    assert isinstance(time, datetime.time), "TimeFromTicks did not return a time object"
-    assert time == datetime.time(10, 0, 0), "TimeFromTicks returned incorrect time"
+    ticks = 1696500000  # Corresponds to local
+    time_var = TimeFromTicks(ticks)
+    assert isinstance(time_var, datetime.time), "TimeFromTicks did not return a time object"
+    assert time_var == datetime.time(*time.localtime(ticks)[3:6]), "TimeFromTicks returned incorrect time"
 
 def test_timestamp_from_ticks():
-    ticks = 1696500000  # Corresponds to 2023-10-05 10:00:00
+    ticks = 1696500000  # Corresponds to 2023-10-05 local time
     timestamp = TimestampFromTicks(ticks)
     assert isinstance(timestamp, datetime.datetime), "TimestampFromTicks did not return a datetime object"
-    assert timestamp == datetime.datetime(2023, 10, 5, 10, 0, 0, tzinfo=datetime.timezone.utc), "TimestampFromTicks returned incorrect timestamp"
+    assert timestamp == datetime.datetime.fromtimestamp(ticks), "TimestampFromTicks returned incorrect timestamp"
 
 def test_binary_constructor():
-    binary = Binary("test")
-    assert isinstance(binary, bytes), "Binary constructor did not return a bytes object"
+    binary = Binary("test".encode('utf-8'))
+    assert isinstance(binary, (bytes, bytearray)), "Binary constructor did not return a bytes object"
     assert binary == b"test", "Binary constructor returned incorrect bytes"