sqlmariadb

Get the next record order by date with same year in MariaDB


I have a table with some holidays, like this:

id date holiday_name
1 2000-10-24 holiday 1
2 2000-09-18 holiday 2
3 2000-01-04 holiday 3

The year is always the same. I want to get the next record after the record with the current date. I have this query now:

SELECT *
FROM holidays
WHERE date > CURDATE()
ORDER BY date
LIMIT 1

It works fine before the end of the year. But if the current date is in December and the next is in January, the query doesn't work because the year is the same and SQL considers it as a passed record. How do I get around this?


Solution

  • Pattern:

    SELECT *
    FROM (
        SELECT *, CONCAT(YEAR(CURRENT_DATE), SUBSTRING(`date` FROM 5)) ndate
        FROM holidays
        UNION ALL
        SELECT *, CONCAT(1 + YEAR(CURRENT_DATE), SUBSTRING(`date` FROM 5))
        FROM holidays
        ) normalized
    WHERE ndate > CURRENT_DATE
    ORDER BY ndate
    LIMIT 1
    

    PS. Adjust for Feb, 29 which may produce incorrect date value.