How to properly query/format dates

Question

I am trying to build a function for my SQlite database using python. The purpose of the function is to pull any date under the calibration_date field that is 1 to 5 days away from the current date.

I've tried messing with my format on the table itself, thinking it was a formatting issue. The function still pulls nothing.

def datecheck():
    conn = sqlite3.connect("Calibration_Monitor.db")
    cur = conn.cursor()
    cur.execute("SELECT Serial, Calibration_Date from Masterlog where date(Calibration_Date) < (SELECT DATETIME('now', '+5 days'))")
    print(cur.fetchall())
    conn.commit()
    conn.close()

Hoping someone can help me solve this mystery!

Anything helps :)

Possible duplicate of [sqlite select with condition on date](https://stackoverflow.com/questions/2309227/sqlite-select-with-condition-on-date) — MyNameIsCaleb, Apr 17 '19 at 19:01
what do you mean `calibration_date field that is 1 to 5 days away from the current`. calibration_date is past or future date??? If pas date then you should subtract 5 days from current date and then compare with the DB dates. — BetaDev, Apr 17 '19 at 19:02

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

You should reference the date without date() if the column is formatted as a date column but use date(Calibration_Date) if it is not.

Also to get the second portion you don't need the extra SELECT in there. See below for a fix and this answer for more information.

def datecheck():
    conn = sqlite3.connect("Calibration_Monitor.db")
    cur = conn.cursor()
    cur.execute("SELECT Serial, Calibration_Date FROM Masterlog WHERE Calibration_Date < date('now', '+5 days')")
    print(cur.fetchall())
    conn.commit()
    conn.close()

Now if what you meant is dates BETWEEN 1 and 5 days away, and not any date under 5 days away, you would want to use something like:

cur.execute("SELECT Serial, Calibration_Date FROM Masterlog WHERE Calibration_Date BETWEEN date('now') AND date('now', '+5 days')")

Edit: If you intend for dates in the past and not dates in the future, you should change it to +5 days.

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 17 '19 at 19:06

MyNameIsCaleb

4,409
1
13
31

1

i think `-5` because date should be in past. – BetaDev Apr 17 '19 at 19:12
I think it's something like a calendar function where he wants to know of things coming up within the next 5 days, and therefore dates are in the future meaning +5 is actually correct. – MyNameIsCaleb Apr 17 '19 at 19:13
Okay got you. Awesome then – BetaDev Apr 17 '19 at 19:14

BetaDev · Answer 2 · 2019-04-17T19:15:09.977

Use -5 NOT +5 if your Calibration_Date date is past date. (Based on my comment above). This will give you all the records older than 5 days in the past.

.......where date(Calibration_Date) < date('now','-5 day')

Something like:

SELECT Serial, Calibration_Date from Masterlog where date(Calibration_Date) < date('now','-5 day')

If you want last five days data

SELECT Serial, Calibration_Date FROM Masterlog WHERE Calibration_Date BETWEEN date('now') AND date('now', '-5 days')

How to properly query/format dates

2 Answers2