marcin2x4
marcin2x4

Reputation: 1449

Python - pandas converts MS SQL date to nvarchar

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.

enter image description here

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?

Upvotes: 1

Views: 258

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123819

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'>

Upvotes: 2

Related Questions