Python: read data from SQL Server into dataframe with dynamic date filter

Question

This question can be thought as a follow up for Python and MSSQL: Filtering techniques while retrieving data from SQL

Basically, I want to retrieve data from SQL Server for a date range set dynamically.

As of now, I am able to run the following code successfully.

import pyodbc
import pandas as pd
cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER=server IP address;DATABASE=dbname;UID=userid;PWD=psswd')
query_pf = "select * from PowerFactor where DeviceTimeStamp between '2019-03-07' and '2019-03-07' "
df_pf = pd.read_sql_query(query_pf,cnxn)

I want the two dates to be set as dynamic. Something like today 00:00:45 and today -1 23:59:59

How it can be done? Perhaps I need to pass a param in dict format?

Jin Thakur · Accepted Answer · 2019-03-11 16:11:57Z

0

DATEADD(hour, 2, '2017/08/25')

Use getdate() for current date if needed Select DATEADD(hour, 2, '2017/08/25')

query_pf = "select * from PowerFactor where DeviceTimeStamp between 
Convert(datetime,'2006-12-30 00:38:54',120) and  Convert(datetime,'2006-12-30 00:38:54',120)

I think this is most flexible solution for you and you can pass the date time you want .The getdate is not goot enough as you cannot run pass old dates function .So generate date time string in right format Convert(datetime,'2006-12-30 00:38:54',120).

DATE & TIME FORMATS
0   select convert(varchar, getdate(), 0)   Dec 12 2006 12:38AM
9   select convert(varchar, getdate(), 9)   Dec 30 2006 12:38:54:840AM
13  select convert(varchar, getdate(), 13)  30 Dec 2006 00:38:54:840AM
20  select convert(varchar, getdate(), 20)  2006-12-30 00:38:54
21  select convert(varchar, getdate(), 21)  2006-12-30 00:38:54.840
22  select convert(varchar, getdate(), 22)  12/30/06 12:38:54 AM
25  select convert(varchar, getdate(), 25)  2006-12-30 00:38:54.840
100 select convert(varchar, getdate(), 100) Dec 30 2006 12:38AM
109 select convert(varchar, getdate(), 109) Dec 30 2006 12:38:54:840AM
113 select convert(varchar, getdate(), 113) 30 Dec 2006 00:38:54:840
120 select convert(varchar, getdate(), 120) 2006-12-30 00:38:54
121 select convert(varchar, getdate(), 121) 2006-12-30 00:38:54.840
126 select convert(varchar, getdate(), 126) 2006-12-30T00:38:54.840
127 select convert(varchar, getdate(), 127) 2006-12-30T00:38:54.840

edited Mar 11, 2019 at 16:11

answered Mar 7, 2019 at 20:16

Jin Thakur

2,81322 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pythondumb Over a year ago

did you mean dateadd(day,1,getdate()) and getdate()

Jin Thakur Over a year ago

you can use select * from PowerFactor where DeviceTimeStamp between Convert(datetime,'2019-12-30 00:00:45',120) and Convert(datetime,'2019-12-30 23:59:59',120)

Collectives™ on Stack Overflow

Python: read data from SQL Server into dataframe with dynamic date filter

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related