Find and sort all the data(dates) after a certain date by month/day and not year in SQLite

Question

I wanna order the data in an sqlite3 database by date. (Day and Month to be precise)

I have a table, the data in the table are in the format YYYY-MM-DD

2003-02-20, 2005-07-16, 2008-11-18, 1998-01-02, 1996-08-27

Here, I wanna find all the data after a certain date(Current date- 'now') and in order. The data is birthdays, so the order should be just based off of Month and Day and shouldn't care about the year.

For example, the data here is

Feb 20, Jul 16, Nov 18, Jan 1, Aug 27

current day= July 28

I want the output to look like

Aug 27, Nov 18, Jan 1, Feb 20, Jul 16

I've looked through many examples and documentations and tried some methods

SELECT * FROM table WHERE birthdays>date('now')

*birthdays are the column where dates are stored*

This gives all the data after ('now') as an output, but it orders it by year as well. Hence, the output will be none since none of the years are greater than current year. I wanna take the year out of the equation and just order it by Month and Day.

How can I do it?

Answer 1

According to the sample data and the expected output you posted, you want to find all birthdays that will occur this year after the date of today. You may use the strftime function to extract month and day as the following:

Select user_id, DOB
From your_table
Where strftime('%m-%d',DOB) > strftime('%m-%d',date())
Order By strftime('%m-%d',DOB)

See a demo from db-fiddle.

Answer 2

you can select the dates, by taking he day of birth, adding the current year ( or the next one if it is smalerer tan the current date)and then selecting the dates that are bigger than the current date limiting 10

SELECT user_id, DOB
FROM your_table
ORDER BY 
CASE WHEN date(strftime('%Y', date('now')) || strftime('-%m-%d', DOB)) > DATE() then date(strftime('%Y', date('now')) || strftime('-%m-%d', DOB))
ELSE date(strftime('%Y', date('now','+1 years'))  || strftime('-%m-%d', DOB)) END
LIMIT 10;

user_id    DOB
5          1996-08-27
10         1996-08-27
15         1996-09-27
13         2008-10-18
3          2008-11-18
8          2008-11-18
4          1998-01-02
9          1998-01-02
14         1998-01-02
1          2003-01-31

db<>fiddle here

Answer 3

You don't need a WHERE clause because all rows of the table should be returned.

What you want is a proper ORDER BY clause:

SELECT * 
FROM tablename
ORDER BY strftime('%m-%d', birthdays) > strftime('%m-%d', 'now') DESC,
         strftime('%m-%d', birthdays);

See the demo.