MySQL Birthday leap month processing

Source: Internet
Author: User
Tags diff

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.