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?
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.