Reputation: 1449
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?
Upvotes: 1
Views: 258
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