How many days does mysql computing have before its birthday (after this year, it will be calculated as per the year)

Mysql calculates the number of days before the birthday (after this year, press the year calculation) Table 1: www.2cto.com name birthday days before the birthday aaaa 2012-12-07 5 bbbb 2013-01-05 34 SQL: Java code SELECT. * FROM (SELECT t. name, t. birthday, case when t. b> 0 THEN t. B ELSE t. a END days FROM (select name, birthday, DATEDIFF (CONCAT (DATE_FORMAT (NOW (), '% y') + 1, DATE_FORMAT (birthday, '-% m-% D'), NOW () a, DATEDIFF (CONCAT (DATE_FORMAT (NOW (),' % y'), DATE_FORMAT (birthday, '-% m-% D'), NOW () B FROM hy _ Member) t) a WHERE. days BETWEEN 0 AND 40; mysql date functions 1. mySQL adds a time interval for the date: date_add () set @ dt = now (); select date_add (@ dt, interval 1 day);-add 1 day select date_add (@ dt, interval 1 hour);-add 1 hour select date_add (@ dt, interval 1 minute);-add 1 minute select date_add (@ dt, interval 1 second ); -Add 1 second select date_add (@ dt, interval 1 microsecond);-add 1 millisecond select date_add (@ dt, interval 1 week);-add 1 week select date_add (@ Dt, interval 1 month);-add select date_add (@ dt, interval 1 quarter) In December January;-add select date_add (@ dt, interval 1 year) in quarter 1 ); -Add the MySQL adddate () and addtime () functions for one year, which can be replaced by date_add. The following is an example of addtime () implemented by date_add (): mysql> set @ dt = '2017-09-09 12:12:33 '; mysql> select date_add (@ dt, interval '01: 15: 30 'hour_second);-plus 1 hour 15 minutes 30 seconds date_add (@ dt, interval '01: 15: 30' hour_second) Result: 13:28:03 mysql> select date_add (@ dt, interval '1 01:15:30 'day_second);-add date_add (@ dt, interval '1 01:15:30' day_second) 13:28:03 date_add () function, adding "1 hour 15 minutes 30 seconds" to @ dt respectively "And" 1 day, 1 hour, 15 minutes, 30 seconds "2. mySQL is the date minus a time interval: date_sub () mysql> select date_sub ('2017-01-01 00:00:00 ', interval '1' day_second ); date_sub ('2017-01-01 00:00:00 ', interval '1' day_second) www.2cto.com MySQL date_sub () Date and time functions are used in the same way as date_add. In addition, there are two subdate () and subtime () functions in MySQL. We recommend that you use date_sub () instead. 3. mySQL alternative date functions: period_add (P, N), period_diff (P1, P2) function parameter "P" format is "YYYYMM" or "YYMM ", the second parameter "N" indicates adding or subtracting N month ). MySQL period_add (P, N): date plus/minus N months. Mysql> select period_add (20080808, 20080808), period_add (200810,-2) | period_add (20080806,-2) | period_add (,-2) | result | MySQL period_diff (P1, p2): Date P1-P2, returns N months. Mysql> select period_diff (200808,200 801); period_diff (200808,200 801) Result: in MySQL 7, these two date functions are rarely used. 4. MySQL date and time subtraction functions: datediff (date1, date2), timediff (time1, time2) MySQL datediff (date1, date2): two dates subtract date1 date2 and return days. Select datediff ('2017-08-08 ', '2017-08-01');-7 select datediff ('2017-08-01 ', '2017-08-08 '); -7 MySQL timediff (time1, time2): two dates minus time1 time2, return the time difference. Select timediff ('2017-08-08 08:08:08 ', '2017-08-08 00:00:00');-08:08:08 select timediff ('08: 08: 08', '00: 00: 00: 00 ');-08:08:08 Note: The two parameter types of the timediff (time1, time2) function must be the same.