I am trying to get current week records from the sqlite3 database or at least get all records that are between the current date and the next 7 days, I have done it via between method but it's not working properly, and it fetches records from the next years.

Code:

self.con = sqlite3.connect('car dealership.db')
self.cursorObj = self.con.cursor()

date = datetime.now()
now = date.strftime(('%d-%m-%Y'))
self.bb = datetime.now()   timedelta(days=7) 
self.newbb_week = self.bb.strftime('%d-%m-%Y')

self.buying_checks_row_main_week = self.cursorObj.execute('SELECT sellername,  paymentdate,  paymentvalue, car FROM cars_buying_checks  WHERE paymentdate BETWEEN (?) AND (?)',(now, self.newbb_week))

self.buying_checks_row_main_week_output = self.cursorObj.fetchall() 

Question: Is there a better method to get the current week records data from the sqlite3 database?

CodePudding user response：

Your problem is that you are formatting your dates in %d-%m-%Y format, which will not work in a BETWEEN expression because it uses string comparison; for example the date 10-01-2999 would be between 09-01-2022 and 11-01-2022. You need to format them as %Y-%m-%d for BETWEEN to work correctly.

If the dates in your table are not formatted as %Y-%m-%d then you will need to update the table to put them into that format, using something like:

UPDATE cars_buying_checks
SET paymentdate = SUBSTR(paymentdate, 7, 4) || '-' || SUBSTR(paymentdate, 4, 2) || '-' || SUBSTR(paymentdate, 1, 2)