SQLite - Order by next upcoming Date (ignore year)

Question

I have a table of persons, and I want to order them by the next upcoming birthday (ignoring year). I've used the method described in this answer:

https://stackoverflow.com/a/20522025/2934340

OR

SQL-code:

SELECT *
FROM _table
ORDER BY SUBSTR(DATE('NOW'), 6)>SUBSTR(birthdate, 6), SUBSTR(birthdate, 6);

The problem is that I use another date format. In the answer above the date format is mm-dd, I want to use dd-mm. Now my dates sort like this:

I want it to sort like this:

17-10-1974
20-10-1993
29-02-1980
03-10-1979 (Because we are at 10-10-2014/past this date)

What changes can I do to my code do achieve this?

This is not one of the [supported date formats](http://www.sqlite.org/lang_datefunc.html). — CL., Oct 10 '14 at 14:58

score 1 · Accepted Answer · answered Oct 11 '14 at 09:17

An easy way to archive this, would be the switching of month and day with the SUBSTR() function:

SELECT *
FROM _table
ORDER BY SUBSTR(DATE('NOW'), 6)>(SUBSTR(birthdate, 4, 3) || SUBSTR (birthdate, 1, 2)), (SUBSTR(birthdate, 4, 3) || SUBSTR (birthdate, 1, 2));

VGe0rge · Answer 2 · 2014-10-11T08:30:48.800

Just a quick solution i could think is the following:

SELECT * 
FROM   (SELECT * 
        FROM   _table 
        WHERE  ( Substr(birthdate, 4, 2) = Substr(Date('now'), 6, 2) 
                 AND Substr(birthdate, 1, 2) > Substr(Date('now'), 9, 2) ) 
                OR ( Substr(birthdate, 4, 2) != Substr(Date('now'), 6, 2) ) 
        ORDER  BY Substr(birthdate, 4, 2), 
                  Substr(birthdate, 1, 2)) 
UNION ALL 
SELECT * 
FROM   (SELECT * 
        FROM   _table 
        WHERE  Substr(birthdate, 4, 2) = Substr(Date('now'), 6, 2) 
               AND Substr(birthdate, 1, 2) < Substr(Date('now'), 9, 2) 
        ORDER  BY Substr(birthdate, 1, 2));

Lets assume that today is 21-02, the first statement will return all the birthdays from 22-02 until 31-01. Then the second statement will return the birthdays that are from 01-02 until 20-02. The Date('now') is represented like YYYY-MM-DD thats why we are comparing Substr(birthdate,4,2) with Substr(Date('now',6,2) for the month comparison. We had to include select statements inside other select statements because otherwise we must place the ORDER BY at the end of the whole statement and that is of course something you don't want.

SQLite - Order by next upcoming Date (ignore year)

2 Answers2