title: How to get latest 7 days and order it if date format is yyyyMdd?
body: <p>I try to describe my code more clearly.</p>
  
  <p>So here is my issue:</p>
  
  <p>I'm stuck with my database , i want to search latest 7 days , but i store my date format is yyyymdd like <code>2017/9/30</code>
  <a href="https://i.stack.imgur.com/Khy7G.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Khy7G.png" alt="enter image description here"></a>
  If i want to search all of my records and order it , my succeed code like this:</p>
  
  <pre><code>public List&lt;Contact&gt; sortingDate() {
          List&lt;Contact&gt; contactList = new ArrayList&lt;&gt;();
          SQLiteDatabase db = this.getWritableDatabase();
          Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_CONTACTS +" ORDER BY\n" +
                  "    SUBSTR(Date, 1, 4) DESC,\n" +
                  "    CASE WHEN INSTR(SUBSTR(Date, 6), '/') = 2\n" +
                  "         THEN '0' || SUBSTR(Date, 6, 1)\n" +
                  "         ELSE SUBSTR(Date, 6, 2) END DESC,\n" +
                  "    CASE WHEN LENGTH(SUBSTR(SUBSTR(Date, 6), INSTR(SUBSTR(Date, 6), '/') + 1)) = 1\n" +
                  "         THEN '0' || SUBSTR(SUBSTR(Date, 6), INSTR(SUBSTR(Date, 6), '/') + 1)\n" +
                  "         ELSE SUBSTR(SUBSTR(Date, 6), INSTR(SUBSTR(Date, 6), '/') + 1) END DESC; ",null);
          //looping through all rows and adding to list
          if (cursor.moveToFirst()) {
              do {
                  Contact contact = new Contact();
                  contact.setID(Integer.parseInt(cursor.getString(0)));
                  contact.setDate(cursor.getString(1));
                  contact.setBeforeMorning(cursor.getString(2));
                  contact.setAfterMorning(cursor.getString(3));
                  contact.setBeforeNoon(cursor.getString(4));
                  contact.setAfterNoon(cursor.getString(5));
                  contact.setBeforeNight(cursor.getString(6));
                  contact.setAfterNight(cursor.getString(7));
                  System.out.println("The result is :" + cursor.getString(1));
                  //Adding contact to list
                  contactList.add(contact);
              } while (cursor.moveToNext());
          }
  
          return contactList;
      }
  </code></pre>
  
  <p>It will print:
  <a href="https://i.stack.imgur.com/t0x5N.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/t0x5N.png" alt="enter image description here"></a></p>
  
  <p>And then i try to add search latest days code to my function, i set a default date <code>2017/09/30</code></p>
  
  <pre><code>// order latest days
      public List&lt;Contact&gt; latestDays() {
          List&lt;Contact&gt; contactList = new ArrayList&lt;&gt;();
          // Database time format is: yyyymdd
          Calendar mCalendar = new GregorianCalendar();
          int mYear = mCalendar.get(Calendar.YEAR);
          int mMonth = mCalendar.get(Calendar.MONTH)+1;
          int mDay = mCalendar.get(Calendar.DAY_OF_MONTH);
          String today = String.valueOf(mYear).concat(String.valueOf(mMonth).concat(String.valueOf(mDay)));
          System.out.println(today+"today");
          SQLiteDatabase db = this.getWritableDatabase();
          // WHERE date...it is what i try the code
          Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_CONTACTS +" WHERE date &gt; (SELECT DATETIME('2017/09/30'," + " ' -7" + " day'))" +
                  " ORDER BY\n" +
                  " SUBSTR(Date, 1, 4) DESC,\n" +
                  " CASE WHEN INSTR(SUBSTR(Date, 6), '/') = 2\n" +
                  " THEN '0' || SUBSTR(Date, 6, 1)\n" +
                  " ELSE SUBSTR(Date, 6, 2) END DESC,\n" +
                  " CASE WHEN LENGTH(SUBSTR(SUBSTR(Date, 6), INSTR(SUBSTR(Date, 6), '/') + 1)) = 1\n" +
                  " THEN '0' || SUBSTR(SUBSTR(Date, 6), INSTR(SUBSTR(Date, 6), '/') + 1)\n" +
                  " ELSE SUBSTR(SUBSTR(Date, 6), INSTR(SUBSTR(Date, 6), '/') + 1) END DESC; ",null);
  
          //looping through all rows and adding to list
          if (cursor.moveToFirst()) {
              do {
                  Contact contact = new Contact();
                  contact.setID(Integer.parseInt(cursor.getString(0)));
                  contact.setDate(cursor.getString(1));
                  contact.setBeforeMorning(cursor.getString(2));
                  contact.setAfterMorning(cursor.getString(3));
                  contact.setBeforeNoon(cursor.getString(4));
                  contact.setAfterNoon(cursor.getString(5));
                  contact.setBeforeNight(cursor.getString(6));
                  contact.setAfterNight(cursor.getString(7));
                  System.out.println("The result is :" + cursor.getString(1));
                  //Adding contact to list
                  contactList.add(contact);
              } while (cursor.moveToNext());
          }
  
          return contactList;
      }
  </code></pre>
  
  <p>I call my function:</p>
  
  <pre><code>private DatabaseHandler db;
  contactList = db.latestDays();
  System.out.println(contactList.size());
  </code></pre>
  
  <p>It shows <code>System.out: 0</code></p>
  
  <p>So what code i miss it if i want to get latest 7 days like <code>2017/9/24</code> <code>2017/9/29</code> <code>2017/9/30</code> in my database ?</p>
  
  <p>Any help would be appreciated. Thanks in advance.</p>