Comparing Dates in SQLLite
Question: Hello Im currently doing a small project in python and sqllite and I have a
csv that has been imported into a database with values under table name :
Members.

Each member has a "Date Joined" field in the format m/dd/yy. Some example
formats are below:

**I cant change the values in the csv because when I turn this assignment in
they're going to use a document with the same format as below**

## Date Joined

5/1/98 6/4/97 7/1/99  
8/1/99  
8/3/99  
11/20/99  
2/2/00  
1/2/99  
2/3/99

One of the questions Im asked is:

to retrieve all member information that have joined after 1999-07-01 (yyyy-mm-
dd) and are from VA (can ignore the VA part)

My query to do this something like this started off as

    
    
    SELECT * FROM Members WHERE "Date Joined" >= "1999-07-01" AND "State"="VA";
    

But my problem is that Im having trouble converting the date (Im guessing its
stored as a string in the database) so it can be compared with "1999-07-01".


Answer: You can try the following query:

    
    
    SELECT *
    FROM yourTable
    WHERE CAST(SUBSTR(SUBSTR(join_date, INSTR(join_date, '/') + 1), INSTR(SUBSTR(join_date, INSTR(join_date, '/') + 1), '/') + 1) AS INTEGER) <= 16 OR
          (
              CAST(SUBSTR(SUBSTR(join_date, INSTR(join_date, '/') + 1), INSTR(SUBSTR(join_date, INSTR(join_date, '/') + 1), '/') + 1) AS INTEGER) >= 99 AND
              CAST(SUBSTR(join_date, 1, INSTR(join_date, '/') - 1) AS INTEGER) >= 7
          )
    

**Explanation:**

Apologies for such an ugly query, but then again the date data you are working
with is also very ugly. The logic of the query is that is will select all
records where the year is `16` or less, _or_ if the year be `99` or greater
_and_ the month be `7` or greater.

The trick here is to carefully use SQLite's string manipulation functions to
extract the pieces we want. To extract the month, use:

    
    
    SUBSTR(join_date, 1, INSTR(join_date, '/') - 1)
    

This will extract everything from the date column up to, but not including,
the first forward slash. To extract the day and year is a bit more work,
because `INSTR` picks up the first matching character. In this case, we can
substring the date to remove everything up and including the first forward
slash. So the day and year can be extracted using:

    
    
    SUBSTR(sub, 1, INSRT(sub, '/') - 1)      -- day
    SUBSTR(sub, INSTR(sub, '/') + 1)         -- year
    

where `sub` is obtained as `SUBSTR(join_date, INSTR(join_date, '/') + 1)`.

