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?

the question makes no sense as a birthday is anual ao everyone has birthdays after today, even if he has today his birthday, maybe you need another approach, for exampe this month till teh end of the year or something like that — nbk, Jul 30 '22 at 11:05
@nbk What I wanna do is find next 10 birthdays after the current date. So I just wanna find out all the birthdays after today and sorted by day and month. So, I believe this is the right approach. What else do you suggest? — Mansss, Jul 30 '22 at 11:09
please read https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query and make a [mre] — nbk, Jul 30 '22 at 11:13
@nbk, I think he wants to get all birthdays that will occur this year after the current date, according to that I proposed an answer. — ahmed, Jul 30 '22 at 11:25
@nbk, Of course, I agree with that, but I replied to your first comment. — ahmed, Jul 30 '22 at 11:42
@Ahmed for a good question, you need to articulate what you are searching a full [mre] can help. — nbk, Jul 30 '22 at 11:47
@nbk, I'm not talking about weather the question was good or not, I replied to you first to explain what I understood from the question. — ahmed, Jul 30 '22 at 11:51

ahmed · Answer 1 · 2022-07-30T12:18:56.280

0

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.

edited Jul 30 '22 at 12:18

answered Jul 30 '22 at 10:49

ahmed

9,071
3
9
22

When I change the date() function to 'now' (Since I want the date to be todays) It gives me an empty list. -------------------------------------- SELECT * FROM birthdays WHERE strftime('%d-%m', date) > strftime('%d-%m', 'now') Order By strftime('%m-%d',date) – Mansss Jul 30 '22 at 11:11
check this, https://www.db-fiddle.com/f/gpjmhFEeGes33DQbRVU2su/2 – ahmed Jul 30 '22 at 11:18
It still returns an empty list – Mansss Jul 30 '22 at 11:20
Yes, `now` is not a date, you can't use it, use `date()` instead of it. – ahmed Jul 30 '22 at 11:22
1

@Mansss: Ahmed's query is good, as his demo clearly shows. If your query doesn't work, it is because you made a mistake when altering Ahmed's query. – Thorsten Kettner Jul 30 '22 at 11:24
1

And here is Ahmed's query altered to show all birthdates starting with the next to come: https://dbfiddle.uk/?rdbms=sqlite_3.39&fiddle=16a3161cdc21a6b825d76330f0555ddf. @Ahmed: You can copy this query into your answer, if you like. – Thorsten Kettner Jul 30 '22 at 11:38

nbk · Answer 2 · 2022-07-30T12:22:46.540

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

score 0 · Answer 3 · answered Jul 30 '22 at 11:58

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.

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

3 Answers3