SQL query date range in python
<p>I was simply trying to query an <code>SQLServer</code> database in a specific date range. Somehow I just can't figure it out myself. Here is what I did:</p>

<pre><code>    import pyodbc
    import pandas as pd
    con = pyodbc.connect("Driver={SQL Server}; Server=link")
    tab = pd.read_sql_query("SELECT * FROM OPENQUERY(aaa, 'SELECT * FROM bbb.ccc WHERE number like (''K12345%'')')",con)

    tab['DT']
    0    2015-09-17 08:51:41
    1    2015-09-17 09:14:09
    2    2015-09-17 09:15:03
    3    2015-09-24 15:20:55
    4    2015-09-24 15:23:47
    5    2015-10-02 08:49:59
    6    2015-10-30 14:08:40
    7    2015-10-30 14:13:38
    8    2015-11-03 14:30:06
    9    2015-11-03 14:30:22
    10   2015-11-04 07:14:40
    11   2015-11-04 10:43:51
    Name: DT, dtype: datetime64[ns]
</code></pre>

<p>Now I thought I should be able to select the records on the dates between <code>2015-09-18</code> and <code>2015-10-02</code> by using the following query. Somehow it failed with error </p>

<blockquote>
  <p>DatabaseError: Execution failed on sql: SELECT * FROM OPENQUERY(aaa, 'SELECT * FROM bbb.ccc WHERE DT between ''2015-09-18'' and ''2015-10-02''')".</p>
</blockquote>

<p>Can someone help explain what I did wrong?</p>

<pre><code> tab2 = pd.read_sql_query("SELECT * FROM OPENQUERY(aaa, 'SELECT * FROM bbb.ccc `WHERE DT between ''2015-09-18'' and ''2015-10-02''')",con)`
</code></pre>

<p>You should use <a href="https://mkleehammer.github.io/pyodbc/#params" rel="nofollow">parameter binding</a>:</p>

<pre><code>tab2 = pd.read_sql_query("SELECT * FROM bbb.ccc WHERE DT between ? and ?", con, params=['2015-09-18', '2015-10-02'])
</code></pre>

<p>The <code>?</code> are placeholders for the values you are passing from the list. The number of <code>?</code>'s must match the number of items from your list.</p>

<p>And I'm not sure why you have a <code>SELECT *</code> wrapped in another <code>SELECT *</code> so I simplified with just the innermost select.</p>

python sql-server tsql