SQL (postgreSQL) how to compare and sort dates ignoring years-CodePudding

I have a table with names and birth_d like this:

|name             | birth_d   |
|Joseph M. Acaba  | 1967-05-17|
|Loren W. Acton   | 1936-03-07|
|James C. Adamson | 1946-03-03|

How I can write a query to sort by their months and days, ignoring years. My mistaken guess is:

`SELECT *, TO_CHAR (birth_d, 'MM-DD') AS "month_day",
CASE WHEN month_day >= "01-01" AND month_day <= "15-01" THEN 'A'
     WHEN month_day >= "12-02" AND month_day <= "29-02" THEN 'E'
     ...
END as "LRanking"
FROM astronauts;`

P.S. I need to do the task using CASE.

CodePudding user response：

Try using the date_part function.

SELECT *,
    date_part('month', birth_d) as month,
    date_part('day', birth_d) as day
FROM users
ORDER BY month, day;

CodePudding user response：

SELECT *
FROM astronauts
ORDER BY extract(doy from birth_d)

doy stands for day-of-year with values of 1 (for 1 January) to 365/366 (for 31 December)