Table of Contents for

MySQL in a Nutshell, 2nd Edition

This function returns the day of the year. January 1 would give a value of 1, and December 31 would normally be 365, except in leap years, when it would be 366. Here is an example:

SELECT DAYOFYEAR('2008-03-01') AS 'FirstDate', 
DAYOFYEAR('2008-02-28') AS 'SecondDate', 
(DAYOFYEAR('2008-03-01') - DAYOFYEAR('2008-02-28')) AS 'Days Apart',
DAYOFYEAR('2008-12-31') AS 'Last Day of Year';

+------------+-------------+------------+------------------+
| First Date | Second Date | Days Apart | Last Day of Year |
+------------+-------------+------------+------------------+
|         61 |          59 |          2 |              366 | 
+------------+-------------+------------+------------------+

In the third field, we are using the function to calculate the number of days from the first date to the second date. Since 2008 is a leap year, the result is 2 and the last field shows 366 for the last day of the year.