I used to use mysql Not many , Yes mysql I'm not very familiar with the functions of , Encounter this problem, the first time Baidu search , Search for these two methods , These two methods are the first blog in Baidu .

Method 1

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)), '%Y')+0 AS age

Method 1 , The author also mentioned the defects , When the date is a future date, the result is 0, Not a negative number ; It's used here 5 Two functions and two operators .

Method 2

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birthday, '00-%m-%d')) AS age

Method 2 , It solves the problem that method 1 is negative , But it looks more complicated ; It's used here 6 A function and 3 Operators .

After reading this post , I just fainted , How could it be so complicated , Previously used Sql Server It's easy . I firmly believe that there must be a simple and efficient way . Soon we found the improved method based on the above methods .

Improved method 1 and method 2

SELECT year( from_days( datediff( now( ), birthdate)));
SELECT YEAR(CURDATE())-YEAR(birthday)-(RIGHT(CURDATE(),5)<RIGHT(birthday,5));

The improved method 1 , A function and an operator are missing , When the date is a future date, the calculation result is 0;
Improved method 2 , still 6 A function and 3 Operators , It looks simpler ; Take the five digits to the right of the date , When the date format is ‘2013-01-01’ When you get ‘01-01’, No problem ; When the date format is ‘2013-1-1’ When abbreviating , Take the five on the right and take out ‘3-1-1’, Can cause errors .

Then according to MYSQL The date function in the help document for comes up with a third way :

Method 3

SELECT FLOOR(DATEDIFF(CURDATE(), @birthday)/365.2422)

Take the number of days before the birthday and current date divided by the actual number of days in a year (365 God 5 Hours 48 branch 46 second ), Then round it up . In this way, only three functions and one operator are used to complete .

then , Soon found a fourth way in foreign websites :

Method four

 SELECT TIMESTAMPDIFF(YEAR, @birthday, CURDATE())

It takes only two functions to do this , It should be the best way .

Tested the above four methods , If the current date is '2017-1-13', When the birthday is ‘2013-1-14’ when , It's only a day before my birthday , leave 4 Year old It's almost a day , The results are 3 year , It doesn't feel very reasonable ; Change method three , Round to get method five :

Method five

SELECT ROUND(DATEDIFF(CURDATE(), @birthday)/365.2422)

The calculated age is the closest to the actual one , But maybe method four is the best way to define age

author :Y Oak Y
link :https://www.jianshu.com/p/64b53c26bea1
Source : Simple books
The copyright belongs to the author . Commercial reprint please contact the author for authorization , Non-commercial reprint please indicate the source .

MySQL More articles on calculating age by date of birth

  1. MySQL Compare the five ways to calculate age based on date of birth

    Method 1 SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)), '%Y')+0 AS age Method 1 , The author also mentioned the defects , That's the day ...

  2. Mysql Compare five ways to calculate age based on date of birth

    Method 1 SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)), '%Y')+0 AS age Method one defect , When the date is a future date ...

  3. Java Calculate age by date of birth

    1. Convert the date of birth string to date format . public static Date parse(String strDate) throws ParseException { SimpleDateFormat ...

  4. mysql Query the age according to the date of birth

    As the title , Find out the age according to the date of birth , I found a lot of them on the Internet , The database can be used , But integrated into mybatis It won't , Report errors . It's the greater than sign , The problem with the less than sign . Do you want me to fix that mistake ? There is no the . Just another way . and sql A big sentence ...

  5. Calculating age by date of birth sql All kinds of database writing

    ORACLE database : ) KINGBASE database : DB2 database : TIMESTAMPDIFF(,

  6. mysql Calculate the age according to the birthday , And query in 18-25 The sentence between

    select id, DATE_FORMAT(birthday,"%Y-%m-%d") birthday, CURDATE() , (year(now())-year(birthd ...

  7. Oracle Calculate age by date of birth

    Method 1 : SELECT TRUNC(months_between(sysdate, birth)/12) AS age from mytable Method 2 : select TRUNC((to_char( ...

  8. java Calculate age by date of birth

    /** * @author jerry.chen * @param brithday * @return * @throws ParseException * Get age by birthday ; */ public ...

  9. oracle The date of birth is used to calculate the age

    select years,months,abs( trunc( newer_date- add_months( older_date,years*12+months ) ) ) days from ( ...

Random recommendation

  1. ViewPager Make a picture browser , Load lots of pictures OOM The problem is fixed

    /** * @author CHQ * @version 1.0 * @date Creation time : 2016/7/26 17:18 * @parameter * @return * Picture viewer * // Sure ...

  2. CSS :focus pseudo-classes

    :focus -- CSS :focus pseudo-classes , Styles for elements that have been focused grammar : :focus CSS edition :CSS2 explain : Styles for elements that have been focused , for example : Form input Input box can input text ...

  3. Oracle User password expiration problem solved

    One . The user password is about to expire , Lead to autotrace Unable to open             If the user's password is about to expire , When logging into the database, you will receive the following prompt :           ERROR:            ORA-2800 ...

  4. careercup- Trees and pictures 4.3

    4.3 Given an ordered array of integers , The elements are different, arranged in ascending order , Write an algorithm , Create a minimum height binary search tree . answer To minimize the height of the constructed binary tree , So for any node , Its left and right subtrees should have the same number of nodes . such as ...

  5. Use form Of target Property mask url jump

    target: Designated open , action URL. Key points : Give Way target Point to the hidden iframe demo: form.jsp <%@ page language="java&qu ...

  6. auth mysql

    DROP TABLE IF EXISTS tky_auth_role;CREATE TABLE tky_auth_role ( roleid MEDIUMINT (8) UNSIGNED NOT NU ...

  7. selenium chromedriver geckodriver iedriverserver download

    chromedriver And chrome The corresponding edition of : chromedriver edition chrome edition v2.9 v31-v34 v2.10 v33-v36 v2.11 v36-v40 v2.12 v ...

  8. MySQL View permissions granted by users

      stay MySQL in , How to see what permissions a user has been granted ? The permissions granted to users may be divided into global level permissions . Database level permissions . Surface level permissions . Column level permissions . Subroutine level permissions . The specific classification is as follows : Global level Global permissions apply to a given ...

  9. insert Insert

    Automatically associate the current time : GETDATE(): Returns the current time and date .

  10. English Phrases with THE – Linking the TH Sound

    English Phrases with THE – Linking the TH Sound Share Tweet Share Tagged With: The Word THE Study En ...