Sqlite Query between 2 dates saved as text

Question

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:

Sample of a few records:

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]

Bill Bell · Accepted Answer · 2017-09-22 13:59:57Z

1

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.

edited Sep 22, 2017 at 13:59

answered Sep 22, 2017 at 3:43

Bill Bell

21.7k6 gold badges48 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Saelyth Over a year ago

could you try that code with a between from 01/01/2001 and 01/01/2099? I had issues with the years. Oh wait, I see now you changed the order of day/motnh/year after the between part.

Saelyth Over a year ago

It was that. I had to reverse the order of the date... thank you for the help :D Accepted your answer as it help me identify the issue.

Bill Bell Over a year ago

You're welcome. I've added to the answer. When I woke up this morning I remembered that I had omitted something.

Collectives™ on Stack Overflow

Sqlite Query between 2 dates saved as text

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related