Comparing Dates in SQLLite
<p>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.</p>

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

<p><strong>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</strong></p>

<h2>Date Joined</h2>

<p>5/1/98
6/4/97
7/1/99<br>
8/1/99<br>
8/3/99<br>
11/20/99<br>
2/2/00<br>
1/2/99<br>
2/3/99</p>

<p>One of the questions Im asked is:</p>

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

<p>My query to do this something like this started off as</p>

<pre><code>SELECT * FROM Members WHERE "Date Joined" &gt;= "1999-07-01" AND "State"="VA";
</code></pre>

<p>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".</p>

<p>You can try the following query:</p>

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

<p><strong>Explanation:</strong></p>

<p>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 <code>16</code> or less, <em>or</em> if the year be <code>99</code> or greater <em>and</em> the month be <code>7</code> or greater.</p>

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

<pre><code>SUBSTR(join_date, 1, INSTR(join_date, '/') - 1)
</code></pre>

<p>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 <code>INSTR</code> 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:</p>

<pre><code>SUBSTR(sub, 1, INSRT(sub, '/') - 1)      -- day
SUBSTR(sub, INSTR(sub, '/') + 1)         -- year
</code></pre>

<p>where <code>sub</code> is obtained as <code>SUBSTR(join_date, INSTR(join_date, '/') + 1)</code>.</p>

python sql database sqlite