CODEWITHSUNDEEP
pythonsqlite
Robit010
Robit010

Reputation: 63

How to properly query/format dates

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 :)

Upvotes: 1

Views: 176

Answers (2)

MyNameIsCaleb
MyNameIsCaleb

Reputation: 4489

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.

Upvotes: 1

BetaDev
BetaDev

Reputation: 4674

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')

Upvotes: 0

Related Questions