Smalldatetime is treated as Datetime4 for MSSQL

[Kipriz]

I continued to test connector-x for our platform and I noticed the following unexpected behavior.
When I select data from columns of smalldatetime from MSSQL, I get exception:

pyo3_runtime.PanicException: not implemented: Datetime4

DDL of the table:

CREATE TABLE [dbo].[tbl_OPInfo](
	[System] [char](3) NOT NULL,
	
	[DDate] [smalldatetime] NOT NULL,
	
	[codedate] [smalldatetime] NOT NULL
	
 CONSTRAINT [PK_tbl_OPInfo_1] PRIMARY KEY NONCLUSTERED (	[System] ASC )
) ON [PRIMARY]

Sample code:

import connectorx as cx
import pyarrow.parquet as pq
import urllib.parse
import pyarrow as pa

sql = """
SELECT  TBL_OPINFO.SYSTEM,
             
             TBL_OPINFO.DDATE,
             
             TBL_OPINFO.CODEDATE
             
FROM ndbcore..tbl_opinfo as TBL_OPINFO
"""

user = urllib.parse.quote("")
password = urllib.parse.quote("")
url = "NDBC\ETL"
mssql_conn_str = f"mssql://{user}:{password}@{url}"

df = cx.read_sql(mssql_conn_str, sql, return_type="arrow2")
pq.write_table(df, './parquet/tbl_opinfo_connectorx-arrow2.parquet', allow_truncated_timestamps=True, use_deprecated_int96_timestamps=True)

I know the following workaround:

SELECT  TBL_OPINFO.SYSTEM,
             
             cast(TBL_OPINFO.DDATE as datetime2) as DDATE ,
             
             cast(TBL_OPINFO.CODEDATE as datetime2) as CODEDATE 
             
FROM ndbcore..tbl_opinfo as TBL_OPINFO

However, it looks strange and hard to train the whole team to write this way.

Any ways how to fix it?

[wangxiaoying]

Hi @Kipriz , good catch. Looks like the underlying mssql client would return smalldatetime not null as datetime4 which we didn't handle earlier (without not null it returns a different type datetimen). Will have an alpha release for this fix 94032b3.

[Kipriz]

Awesome! Version 0.2.6.a2 works for this case!