Reputation: 1734
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:
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
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