1

Given a table with a date column, and given a query that selects dates in a given range of the year (like "November 20th through February 20th"), and where the year portion of the date doesn't matter (such as upcoming birthdays), how can the results be sorted by month in the correct chronological order for the selected time range?

For example, if the query grabs all dates between November 20th and February 20th regardless of year, it should sort them in the order:

Nov -> Dec -> Jan -> Feb

But a regular sort by month actually produces:

Jan -> Feb -> Nov -> Dec

Or an inverted sort produces:

Dec -> Nov -> Feb -> Jan

both of which are wrong.

Here's a sample query with a three day range at the end of the year:

SELECT EventDate
   FROM Events
   WHERE TIMESTAMPDIFF(YEAR, EventDate, "2012-12-28" + INTERVAL 3 DAY)
      > TIMESTAMPDIFF(YEAR, EventDate, "2012-12-28" - INTERVAL 1 DAY)
   ORDER BY DATE_FORMAT(EventDate, "%m-%d") DESC

It produces:

2012-01-01
2008-01-01
2008-01-01
1987-01-02
1994-12-28

Where it should produce:

1994-12-28
2012-01-01
2008-01-01
2008-01-01
1987-01-02

Is there a way to get it to sort by month and day in a "going forward from range start" order?

I searched here and on SO but didn't find an answer that dealt with both year end wrapping of months and a scenario where the year in the date field isn't relevant for sorting.

Update: The range should always sort forward, so if the range is Feb to Nov, it does a standard sort of Feb - > Mar -> Apr .... If the range is Nov to Feb, it sorts Nov -> Dec -> Jan ....

Although I don't anticipate that being an issue since I don't need to query more than a 3 month window.

  • 1
    How do you distinguish the cases when you query "between November 20th and February 20th" and "between February 20th and November 20th"? – mustaccio May 30 '16 at 12:08
  • Oracle or MySQL? Can you please update the tag? – Julien Vavasseur May 30 '16 at 12:11
  • I've added the MySQL tag, and added more info about sorting. – Nick May 30 '16 at 12:15
1

Try adding a specific field to order by. That field is the value of the month if it is greater than the value of current month, and add 12 if it is less (or equal), then order by that field:

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2016-05-30 15:02:45 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from dates;
+------------+
| d          |
+------------+
| 2012-01-01 |
| 2008-01-01 |
| 2008-02-01 |
| 1987-01-02 |
| 1994-12-28 |
| 1987-05-02 |
| 1994-11-28 |
+------------+
7 rows in set (0.00 sec)

mysql> select d, month(d), if(month(d)>month(now()), month(d), month(d)+12) ord from dates order by ord;
+------------+----------+------+
| d          | month(d) | ord  |
+------------+----------+------+
| 1994-11-28 |       11 |   11 |
| 1994-12-28 |       12 |   12 |
| 2012-01-01 |        1 |   13 |
| 2008-01-01 |        1 |   13 |
| 1987-01-02 |        1 |   13 |
| 2008-02-01 |        2 |   14 |
| 1987-05-02 |        5 |   17 |
+------------+----------+------+
7 rows in set (0.00 sec)

mysql> select d from (select d, month(d), if(month(d)>month(now()), month(d), month(d)+12) ord from dates order by ord)x;
+------------+
| d          |
+------------+
| 1994-11-28 |
| 1994-12-28 |
| 2012-01-01 |
| 2008-01-01 |
| 1987-01-02 |
| 2008-02-01 |
| 1987-05-02 |
+------------+
7 rows in set (0.00 sec)
  • Perfect answer. Thanks alot. – rahul Oct 7 at 10:22
1

Test data

create table dates ( d date);
insert dates values ('2012-01-01')
,('2008-01-01')
,('2008-02-01')
,('1987-01-02')
,('1994-12-28')
,('1987-05-02')
,('1994-11-28')

Query

select d, DATE_FORMAT(d, "%m-%d") 
from dates
join (select 11 ms, 2 me) params
where ms<=me and MONTH(d) between ms and me
  or ms>me and (MONTH(d) >=ms or MONTH(d) <= me)
order by case when MONTH(d) < ms then 100+MONTH(d) else MONTH(d) end
  • How does adding 100 to the month fix the sorting? – Nick May 30 '16 at 16:17
  • Not necessary 100. Having 1<=MONTH(d)<=12 , 13 is just enough. And it's added to months which are less then starting value ms, which is only possible if parameter ms (starting month) is greater then parameter me (end month), that is if period requested crosses the year end. – Serg May 30 '16 at 17:52
  • 2
    Since the type control in mysql is not particulary strict an alternative solution is: order by MONTH(d) + 100 * (MONTH(d) < ms). MONTH(d) < ms is evaluated to 0 or 1. – Lennart May 30 '16 at 19:45
  • @Lennart: This is what I ended up using in combination with Jehad's answer. The answer above doesn't take days into account and relies on explicit month numbers being set. – Nick May 31 '16 at 8:18
0

Improved version of Serg's solution. This one takes in account days.

create table events (event_date date);
insert events values
 ('2012-01-01')
,('2008-01-01')
,('2008-02-01')
,('1987-01-02')
,('1994-12-28')
,('1987-05-02')
,('1994-11-28')

select
    *
from
    events
order by
    case
        when
            MONTH(event_date) < MONTH(curdate())
            or (
                MONTH(event_date) = MONTH(curdate())
                and DAY(event_date) < DAY(curdate())
            ) then 100 + MONTH(event_date)
        else MONTH(event_date)
    end,
    DAY(event_date)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.