Python - pandas converts MS SQL date to nvarchar

Question

My python code runs read_sql... method on a sample MS SQL Server query.

One of the columns - system_type_name - indicates type date while running in SSMS.

Query executed with below code gives me nvarchar(10) type:

Config.json:

{"Drive": "SQL Server", "Server": "server_name", "Database":...... "UID":........ "PWD":......}

Code:

import pandas as pd import pyodbc query = ''' DECLARE @dt DECLARE @sql nvarchar(max) = N'procedure_name 0, ''1900-01-01'', @dt'; SELECT system_type_name FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 0) ''' cnxn = connect_to_db("config.json", False) src = pd.read_sql(query, cnxn) print(src)

df result:

 num_legs system_type_name nvarchar(10)

Is this some conversion bug/issue pandas is having with date type?

Regarding my answer (now deleted). It was me not understanding your question.... Terribly sorry for the confusion! — squillman
– squillman, Commented Jan 17, 2022 at 17:53
What ODBC driver are you using? "SQL Server"? "ODBC Driver 17 for SQL Server"? Something else? — Gord Thompson
– Gord Thompson, Commented Jan 17, 2022 at 21:38

Gord Thompson · Accepted Answer · 2022-01-17 23:39:24Z

The ancient "SQL Server" driver returns a string representation for several T-SQL types. Newer ODBC drivers return more specific types. For example:

# with DRIVER=SQL Server # print(type(crsr.execute("SELECT CAST('2022-01-17' AS DATE) AS d").fetchval())) # <class 'str'> # with DRIVER=ODBC Driver 17 for SQL Server # print(type(crsr.execute("SELECT CAST('2022-01-17' AS DATE) AS d").fetchval())) # <class 'datetime.date'>

Collectives™ on Stack Overflow

Python - pandas converts MS SQL date to nvarchar

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related