0

I have 3 entries added in my database, one of the fields contain a date saved as a string, when i query with "SELECT * FROM 'TABLE' ", i get all 3 of them, then when i print their corresponding date i get, in this example

2019-5-4 14:20:00

2019-6-4 14:20:00

2019-7-4 14:20:00

But when i try to use "SELECT * FROM 'TABLE' WHERE DATE BETWEEN '2019-4-1' AND '2019-7-10'" the query contains first two results, but not the last one. It will contain the 3rd one when i change the month from 7 to 8, or when the day in second date is changed from double digits to one digit, so everything from 4 to 9, instead of 2019-7-10 there will be 2019-7-9.

How can I get all the results using double digits as days in second date?

Wiktor
  • 885
  • 1
  • 9
  • 23
  • https://stackoverflow.com/questions/29971762/sqlite-database-select-the-data-between-two-dates/29971871 – mchts May 04 '19 at 20:20
  • 4
    Change the format of your dates to `YYYY-MM-DD`. The format you use is not comparable, because in **SQLite** there is no `Date` data type. The dates are stored as `TEXT`, so:`'2019-10-10'` is considered less than `'2019-7-10'`. – forpas May 04 '19 at 20:44

1 Answers1

2

If you want to compare dates ignoring timestamps, then you have to do it explicitly: SELECT * FROM 'TABLE' WHERE DATE(DATE) BETWEEN '2019-04-01' AND '2019-07-10'"

thelawnmowerman
  • 11,956
  • 2
  • 23
  • 36
  • I don't think it works, i'm assuming one DATE is name of column containing a date right? When i add this (date) the query gives back 0 results – Wiktor May 04 '19 at 20:31
  • 1
    Are you adding the proper ISO zero padding to the months and days? `2019-04-01` instead of `2019-4-1` – thelawnmowerman May 04 '19 at 20:35
  • `DATE()` is a function name you should use if you are not comparing timestamps (the hour of the day, just the date). If the column name is `signup_date`, then you should use `DATE(signup_date)`. If the column name is `date`, then you should use `DATE(date)`. In case, of course, that SQLite is allowing you to name a column with a reserved word of its own syntax, as `DATE`. Allowed or not in SQLite, it is not allowed in other database engines and a bad and dangerous habit, IMHO. – thelawnmowerman May 04 '19 at 20:38
  • I was not saving date Strings in the database as you said `2019-04-01`, but as `2019-4-1`. Now that i've changed it, everything works. Looks like when you skip the `0` in front of day and month numbers, it kinda works, but not exactly. – Wiktor May 04 '19 at 20:49
  • 1
    In SQLite `DATE('2019-7-10')` returns NULL. – forpas May 04 '19 at 20:50
  • But `DATE('2019-07-10')` works, as it should be written. Please read my answer ;-) – thelawnmowerman May 04 '19 at 20:56
  • @Wiktor, that's because when you omit those zeros, SQLite is not detecting the values as dates, so it stores them as regular strings (and therefore, it compares the values in queries as it compares regular strings, "cat", "guitar", "1234", "2019-1-4"... none of them are dates for the engine). The moment you insert proper dates, the engine is able to compare them as dates. I am glad it works now! – thelawnmowerman May 04 '19 at 20:59