CODEWITHSUNDEEP
python-3.xsqlite
Lucas Fernandez
Lucas Fernandez

Reputation: 96

get only month from date in sqlite database

I have a data base with a date column and i want to retrieve items that have an specific month in the date field. I've tried something like this.

The date field is in dd/mm/yyyy format

var = '09'
with conn:
    c.execute(f"SELECT * FROM sales WHERE strftime('%m',date) = '{var}'")
    x = c.fetchall()

Ive also tried

SELECT * FROM sales WHERE date('%m',date) = '{var}'

in every case i keep getting no errors but x is empty

note = i'll worry of injections after it works and there are several items in my database that match the "month = 09" criteria

Upvotes: 1

Views: 1011

Answers (1)

forpas
forpas

Reputation: 164089

You can use the function strftime() only with the format 'YYYY-MM-DD', so either you should change the format of your dates or use the function SUBSTR() to get the month:

c.execute(f"SELECT * FROM sales WHERE substr(date, 4, 2) = '{var}'")

if you pass var padded with '0' at the left (if needed), so the comparison is alphabetical,
or:

c.execute(f"SELECT * FROM sales WHERE substr(date, 4, 2) + 0 = {var}")

so the comparison will be numerical.

In any case it is safer to write the code like this:

c.execute("SELECT * FROM sales WHERE substr(date, 4, 2) + 0 = ?", (var,))

where var is an integer variable,
or:

c.execute("SELECT * FROM sales WHERE substr(date, 4, 2) = ?", (var,))

where var is a string variable padded with '0' at the left (if needed) like your code.

Upvotes: 1

Related Questions