pyodbc results return empty if sql has a date condition

  datetime, pandas, pyodbc, python, sql
import pyodbc
import pandas as pd


# Connection string
conx_string = "driver={SQL Server Native Client 11.0}; server=; database=; UID=; PWD="

# Query to run
query = """
select new_name, CreatedOn, new_status, new_PaperApplicationSubmitted 
from new_application where createdon >= '01-Feb-2021'
and CreatedOn <= '1-Mar-2021'
and new_ApplicationType = 2 
and new_PaperApplicationSubmitted = 1
"""

# Creating connection and running the query
with pyodbc.connect(conx_string) as conx:
cursor = conx.cursor()
cursor.execute(query)
query_data = cursor.fetchall()


# Define column names
query_columns = [column[0] for column in cursor.description]    


# dump records in dataframe
df = pd.DataFrame.from_records(data=query_data, columns=query_columns)
# Print data type
print(df.dtypes)
print(df)

cursor.close()
conx.close()

I tried different variations of date and also tried to convert the date "where
createdon >= convert(datetime,’2021-05-01T00:00:00.000′)
and CreatedOn <= convert(datetime,’2021-06-01T23:59:59.997′)"

The SQL pulls results when I run in SQL server management studio

Source: Python Questions

LEAVE A COMMENT