CODEWITHSUNDEEP
pythonpython-3.xsqlite
Saelyth
Saelyth

Reputation: 1734

Sqlite Query between 2 dates saved as text

My database have a column named Fecha for date records saved as dd/mm/yyyy strings (TEXT) (note the /, not dd-mm-yyyy). That might sound confusing so check the attached screenshots.

Type of the column:

enter image description here Sample of a few records:

enter image description here

Now, my goal is to make a query between 2 dates, so after all my research I ended up here:

SELECT * 
FROM Facturas 
WHERE substr(Fecha,7)||'/'||substr(Fecha,4,2)||'/'||substr(Fecha,1,2) 
BETWEEN '01/01/2001' AND '12/09/2099'

However, that doesn't works and I don't get why. Any clue?
Note also: I am not able to convert the storage of that column to yyyy-mm-dd as I need the data stored specifically in that way.

Fix: Both of the values after BETWEEN must use yyyy/mm/dd also, so I just made it goes that way and it works now.

def reverse_date(date)
    split = date.split("/")
    return split[2] + "/" + split[1] + "/" + split[0]

Upvotes: 0

Views: 55

Answers (1)

Bill Bell
Bill Bell

Reputation: 21663

First I create a table somewhat like yours.

>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> c = conn.cursor()
>>> c.execute('''CREATE TABLE Facturas (Fecha TEXT, Something INTEGER)''')
<sqlite3.Cursor object at 0x00000000067D2110>
>>> from datetime import datetime
>>> for d in range(1,31):
...     c.execute('''INSERT INTO Facturas VALUES (?,?)''', (datetime(2017,6,d).strftime('%d/%m/%Y'), d))
...

This verifies that the dates look like those in your table.

>>> for row in c.execute('''SELECT * FROM Facturas'''):
...     print(row)
...     
('01/06/2017', 1)
('02/06/2017', 2)
('03/06/2017', 3)
('04/06/2017', 4)
('05/06/2017', 5)
('06/06/2017', 6)
('07/06/2017', 7)
('08/06/2017', 8)
('09/06/2017', 9)
('10/06/2017', 10)
('11/06/2017', 11)
('12/06/2017', 12)
('13/06/2017', 13)
('14/06/2017', 14)
('15/06/2017', 15)
('16/06/2017', 16)
('17/06/2017', 17)
('18/06/2017', 18)
('19/06/2017', 19)
('20/06/2017', 20)
('21/06/2017', 21)
('22/06/2017', 22)
('23/06/2017', 23)
('24/06/2017', 24)
('25/06/2017', 25)
('26/06/2017', 26)
('27/06/2017', 27)
('28/06/2017', 28)
('29/06/2017', 29)
('30/06/2017', 30)

I select the records from 6 June through 20 June inclusive.

>>> for row in c.execute('''SELECT *  FROM Facturas WHERE substr(Fecha,7)||'/'||substr(Fecha,4,2)||'/'||substr(Fecha,1,2) BETWEEN '2017/06/06' AND '2017/06/20' '''):
...     print(row)
... 
('06/06/2017', 6)
('07/06/2017', 7)
('08/06/2017', 8)
('09/06/2017', 9)
('10/06/2017', 10)
('11/06/2017', 11)
('12/06/2017', 12)
('13/06/2017', 13)
('14/06/2017', 14)
('15/06/2017', 15)
('16/06/2017', 16)
('17/06/2017', 17)
('18/06/2017', 18)
('19/06/2017', 19)
('20/06/2017', 20)

Notice that the BETWEEN operator works now because the format of the dates from your expression matches the format of the dates 2017/06/06 and 2017/06/20.

Addendum: I forgot to mention, not only does that format match the dates in the BETWEEN expression it puts the parts of the dates in significance order, ie, year, month, day.

Upvotes: 1

Related Questions