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

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?

CodePudding user response：

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.

CodePudding user response：

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.