Not SELECTING data between two dates that are from different months in SQLite

Question

I am trying to write XLSX from an SQLite table with a WHERE clause between two selected dates.

Below is my example:

search_from = "19-09-2020"
search_to   = "19-10-2020"

conn=sqlite3.connect('project.db')
writer      = pd.ExcelWriter("data.xlsx", engine='xlsxwriter')
data = "SELECT Pro_ID, Pro_Name, Pro_City FROM table WHERE `Started_Date` >= ? and `Started_Date` <= ?"

df_data         = pd.read_sql(data, conn, params=(search_from, search_to))
df_data.to_excel(writer, sheet_name="Data")

It is selecting data if search_date and started_date are the same month, but not if they are in different months.

use BETWEEN - refer https://stackoverflow.com/questions/5125076/sql-query-to-select-dates-between-two-dates — sharathnatraj, Oct 26 '20 at 13:50
Use only dates in the format YYYY-MM-DD for SQLite tables and compare them to date strings of the same format and your code will work fine. — forpas, Oct 26 '20 at 14:11
@ forpas Actually for now i want to use DD-MM-YYYY. and If i use YYYY-MM-DD this is my above query fine or is any modification required ? — Mano, Oct 26 '20 at 17:12
SQLite stores dates as TEXT (strings) so use only the format YYYY-MM-DD in the table and for the parameters that you pass: search_from = "2020-09-19" and search_to = "2020-10-19" — forpas, Oct 26 '20 at 17:36

score 0 · Answer 1 · edited Nov 01 '20 at 19:33

0

Date strings need to converted into a valid date format for SQLite as below:

from dateutil import parser

search_from = "19-09-2020"
search_to   = "19-10-2020"

search_from = parser.parse(search_from, parser.parserinfo(dayfirst=True))
search_from = search_from.strftime('%Y-%m-%d')

search_to = parser.parse(search_to, parser.parserinfo(dayfirst=True))
search_to = search_to.strftime('%Y-%m-%d')

Then, you can update your query as below using between:

query = "select Pro_ID, Pro_Name, Pro_City from table where Started_Date between search_from and search_to"

Or you can use:

query = "select Pro_ID, Pro_Name, Pro_City from table where Started_Date >= search_from  and Started_Date <= search_to"

Of course, you need to bind variables to a SQL query. In your case:

data    = "select Pro_ID, Pro_Name, Pro_City from table where Started_Date between ? and ?"
df_data = pd.read_sql(data, conn, params=(search_from, search_to))

Or:

data    = "select Pro_ID, Pro_Name, Pro_City from table where Started_Date >= ? and Started_Date <= ?"
df_data = pd.read_sql(data, conn, params=(search_from, search_to))

edited Nov 01 '20 at 19:33

Peter Mortensen

30,738
21
105
131

answered Oct 26 '20 at 13:54

ozturkib

1,493
16
28

Why do you expect strftime() and between to work? What is it that makes your 2nd query *exclusive*? – forpas Oct 26 '20 at 14:02
In order to format the date string. For instance > search_from = "19-09-2020" – ozturkib Oct 26 '20 at 14:04
strftime() formats only valid dates YYYY-MM-DD. For a date like 19-09-2020 it returns null. Also strftime() with the `%s` modifier does not format even valid dates. Read about it: https://sqlite.org/lang_datefunc.html – forpas Oct 26 '20 at 14:07
@forpas Thanks for the comment. I have updated the answer based on that. – ozturkib Oct 26 '20 at 14:22
@ forpas and @ ozturkib. Thank You for your response. @ozturkib is required to use `from dateutil import parser` for both queries? And in second query, is no need to use Quotation mark? – Mano Oct 26 '20 at 17:09
Yes, you need to format date string properly for sqlite needs, parser part is handling this side. So you need it in your scenario. – ozturkib Oct 26 '20 at 17:18
@ozturkib I tried Above answer, both queries not working. – Mano Oct 27 '20 at 05:10
@Mano you should not directly copy and paste them, you need to bind them for your case. However, I have updated the answer which takes into account binding as well. – ozturkib Oct 27 '20 at 07:13
@ozturkib. I did not do copy past. I bind them in to my query before your update then only i commented. still not working. Also I changed `s_search_from` to `search_from` tried as like your update. – Mano Oct 27 '20 at 09:36
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/223684/discussion-between-ozturkib-and-mano). – ozturkib Oct 27 '20 at 09:40

Not SELECTING data between two dates that are from different months in SQLite

1 Answers1