title: How to count the records in a Sqlite table that match a date restriction?
body: <p>I'm using a CursorLoader to return a record count from my Content Provider, and I need to count only those records that are 7 or fewer days old. My attempts to make this work have so far yielded nothing but frustration, as the count is <strong>always</strong> zero. I have limited experience with Sqlite and CursorLoaders, and I suspect I'm making a very simple error . Here is my code:</p>
  
  <pre><code>String strCriteria = "SELECT julianday('now') - julianday('" + Session.DATE_STRING
              + "','gregorian') &lt;8";
  
  return new CursorLoader(getSherlockActivity(), Session.CONTENT_URI, 
             new String[] {"count(" + Session.TABLE_NAME + "." + Session._ID +") AS Count " }, 
            strCriteria, null,null);  
  </code></pre>
  
  <p>Can anyone see where I am going wrong? </p>
  
  <p>I should add that the DATE_STRING column stores the date as a string in the yyyy-MM-dd format. I do actually have another column storing date as a long, but I haven't figured out how to do Sqlite date queries using numeric values either.</p>
  
  <p>UPDATE to question: if I replace the Session.DATE_STRING part of the strCriteria definition with a text date, it all works perfectly. For example, if I use the following code :</p>
  
  <pre><code>String strCriteria = "SELECT julianday('now') - julianday('" + "2013-05-08"
              + "','gregorian') &lt;8";
  </code></pre>
  
  <p>...instead of what I've got above, then it all works perfectly. This leads me to believe that my code is not actually putting the <strong>value</strong> of the Session.DATE_STRING column into the string, but rather something else, most likely the actual string name of the column. I do not know how to get around this, as I was assuming that the query would actually fill in the proper data value at runtime. Any suggestions would be greatly appreciated!</p>
  
  <p>EDIT WITH SOLUTION: I've figured out how to make it work properly. It's quite simple really - I had to remove the ' from around Session.DATE_STRING to ensure that the value of the column was inserted, rather than the column name. So, this is how the strCriteria needed to be defined:</p>
  
  <pre><code>String strCriteria = "SELECT julianday('now') - julianday(" + Session.DATE_STRING
              + ",'gregorian') &lt;8";
  </code></pre>