MySQL Birthday leap month processing

1. The year of Popular Science:

①, not the whole hundred years can be divisible by 4 for a leap year. (such as 2004 years is a leap year, 2100 is not a leap year) ②, the whole century can be divisible by 400 is a leap year. (as 2000 is a leap year, 1900 is not a leap year) 2. Example:For example: The current date is August 21, 2016, someone was born on February 29, 1972, and the last birthday of the user after query should be March 1, 2017 (not a leap year). If the current date is January 20, 2016, then the query should return February 29, 2016 (leap year). 3. Initial data

Install the MySQL official sample database employees first. Not installed can be consulted: "Mac install MySQL official sample database Employee"

• Script actions:

-- Creating Table EmployeesCREATE table employees like employees.employees;-- Insert the Employees table data from the employees library into your own table insert into employeesSelect* fromEmployees.employees limit0,Ten;--new data, birthday for leap year 1972-02-29INSERT INTO EmployeesSelect '10011','1972-02-29','Jiang','David','M','1990-2-20';

4. Query user and birth information

• Statement:

--Query user and birth information Select '  '  as  as  from Employees e;

• Query Result:

5. Implement

5.1 Query the number of years for the current date, the current date, and the birthday interval.

• • Code:

  • Select '  '  as Name,     e.birth_date    as BirthDay,    (year (today))-year(e.birth_date ) diff, as      today from Employees E

  • Results:

　　　　　　

　　　　　　　　

5.2 Check The birthday of the year and the birthday of the next year.

• • Code:

    SelectName,birthday,today, Date_add (Birthday, Interval diff year) Curr,-- Birthday date_add (Birthday, Interval diff+1Year) Next--next year's birthday from (    SelectConcat (E.last_name,'  ', E.first_name) asName, E.birth_date asBirthDay, ( Year (now))-Year (E.birth_date)) diff, now () asToday fromEmployees e) asA

  • Query results

　　　　　　

　　　5.3 Date of birth is 29th, the year or the next birthday is 28th, the birthday date plus 1 days

• • Code:

    SelectName,birthday,today, Date_add (Curr, Intervalif(Day (Birthday) = in&& Day (Curr) = -,1,0) day) asCur-- years after the leap year's birthday Date_add (Next, Intervalif(Day (Birthday) = in&& Day (Next) = -,1,0) day) asNext--next year's birthday after leap years run from (    SelectName,birthday,today, Date_add (Birthday, Interval diff year) Curr,--Birthday Date_add (Birthday, Interval diff+1Year) Next--next year's birthday from (    SelectConcat (E.last_name,'  ', E.first_name) asName, E.birth_date asBirthDay, ( Year (now))-Year (E.birth_date)) diff, now () asToday fromEmployees e) asa) asB

  • Query Result:

　　　　　　

5.4 The final code, if the birthday of the year is past, return to the next year's birthday.

• • Code:

    SelectName,birthday,if(Cur>today, cur, next) asBirth_day--If the birthday of the year is greater than the current date, the birthday is this year, otherwise the next year from(    SelectName,birthday,today, Date_add (Curr, Intervalif(Day (Birthday) = in&& Day (Curr) = -,1,0) day) asCur,--years after the leap year's birthday Date_add (Next, Intervalif(Day (Birthday) = in&& Day (Next) = -,1,0) day) asNext--next year's birthday after leap years run from (        SelectName,birthday,today, Date_add (Birthday, Interval diff year) Curr,--Birthday Date_add (Birthday, Interval diff+1Year) Next--next year's birthday from (        SelectConcat (E.last_name,'  ', E.first_name) asName, E.birth_date asBirthDay, ( Year (now))-Year (E.birth_date)) diff, now () asToday fromEmployees e) asa) asb) asC

  • Query Result:

　　　　　　

　　

MySQL Birthday leap month processing