Latchy Latchy - 14 days ago 5
MySQL Question

MySQL number of days until next occurrence of a date?

I'm looking for a way to have an accurate countdown until Christmas, in number of day. I tried...

(SELECT DATEDIFF(CONCAT(YEAR(CURDATE()), '-12-25'), NOW())) as days


However this will return a negative date between Christmas and New Years.
I thought I could just add 365 if the number was negative but that won't account for leap years. Any suggestions for a better solution?

Many thanks!

Answer

This is pretty crude, but should do the trick. See if the result is negative, and if so then add one to the year:

Select Case DateDiff(Concat(Year(CurDate()), '-12-25'), Now())) < 0
         Then DateDiff(Concat(Year(CurDate())+1, '-12-25'), Now())) 
         Else DateDiff(Concat(Year(CurDate()), '-12-24'), Now())) End As "days"
Comments