Select Where date is greater than or equal to today in SQLite

Question

I have dates stored in my Database in a dd/mm/yyyy format and I am looking to only show items where the date is equal to or great than today.

I have tried WHERE date(fixtures.date) >= date('now') but I got no results.

Below is my query, any help would be greatly appreciated

SELECT fixtures.id, 
       fixtures.team_1_id, 
       fixtures.team_2_id, 
       fixtures.date, 
       fixtures.time, 
       fixtures.pitch, 
       teams.team_name, 
       teams_1.team_name AS awayTeam, 
       leagues.league_name 
FROM   fixtures 
       INNER JOIN teams 
               ON fixtures.team_1_id = teams.id 
       INNER JOIN teams AS teams_1 
               ON fixtures.team_2_id = teams_1.id 
       INNER JOIN teams_vs_leagues 
               ON teams.id = teams_vs_leagues.team_id 
       INNER JOIN leagues 
               ON teams_vs_leagues.league_id = leagues.id 
WHERE  date(fixtures.date) >= date('now') 
ORDER  BY fixtures.date DESC 

Answer 1

Why do you say your dates are store in dd/mm/yyyy format? According to SQLite docs

1.2 Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

1. TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").

2. REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.

3. INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

Basically this query won't work

select 1 where '01-08-2016' >= date('now')

but this will

select 1 where '2016-08-01'>= date('now')

So can you verify if your date fixtures.date is format as 'yyyy-MM-dd' otherwise your query won't work. Additionally remember to use date('now', 'localtime') to get your local time.

If your date is well formated you can try to do something like

SELECT fixtures.id
FROM   fixtures
WHERE  fixtures.date >= date('now')

if you do get results with this, then the joins are not matching any row.

For further information you can check this answer