Mega Code Archive

 

Categories / MySQL / Date Time

 

Another way to compute a years length is to compute the date of the last day of the year and pass it to DAYOF

YEAR( ): mysql> mysql> SET @d = '2003-04-13'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT DAYOFYEAR(DATE_FORMAT(@d,'%Y-12-31')); +---------------------------------------+ | DAYOFYEAR(DATE_FORMAT(@d,'%Y-12-31')) | +---------------------------------------+ |                                   365 | +---------------------------------------+ 1 row in set (0.00 sec) mysql> SET @d = '2004-04-13'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT DAYOFYEAR(DATE_FORMAT(@d,'%Y-12-31')); +---------------------------------------+ | DAYOFYEAR(DATE_FORMAT(@d,'%Y-12-31')) | +---------------------------------------+ |                                   366 | +---------------------------------------+ 1 row in set (0.00 sec) mysql>