Calculating a person's age from their date of birth is pretty simple. We just have to find the difference between the date of birth and the current date in years, and as a result, we will get the age of the person. In SQL, to find the age from the date of birth, we will need to use these functions: Let's see how to use these functions to convert the date of birth of
a person into their age in the next section. We can find the age of a person from their date of birth by using this formula: In the above formula, we are first finding the difference between the current date (NOW()) and the date of birth (in YYYY-MM-DD) using the DATEDIFF() function. The difference is then passed to the FROM_DAYS() function that calculates the time span of the date difference in the YYYY-MM-DD format. The DATE_FORMAT() function is used to get the year from the YYYY-MM-DD using the format specifier '%Y', returning the age of the person as a 4-digit number (for e.g., 00320032 or 00290029). To get the age in a simple INT form without the unnecessary 0s, we add the 4-digit number with 00, resulting in the correct age format. Examples of Calculating Age from Date of Birth in SQLLet us have a look at some examples to have a clear understanding of this concept. Demo DatabaseEmployee Table
This table has 4 columns: emp_idemp\_id, first_namefirst\_name, last_namelast\_name, and date_of_birthdate\_of\_birth. The PRIMARY KEY is emp_idemp\_id of every employee. There are 5 employees in this table. Every example will make an update to this table and not an already updated table. Displaying the age of each employee in the tableWe will display each employee's age using their date of birth from the Employee table. Code:
Output:
In the above example, we are displaying the details of each employee with their age using their date of birth. Updating the table to add a new column for ageWe will now update the table to add a new column to store each employee's age, instead of just displaying them. Code:
Output:
In the above example, we are adding a new column in the Employee table, named age, which contains each employee's age calculated using their date of birth. Finding the age of someone using their date of birthWe will now find the age of a person, whose date of birth is stored in a variable. Code:
Output: In the above example, we have calculated the age of a person using a variable that stores their date of birth. Learn More About Time and Date Format in SQLThis is the complicated side of SQL, when dealing with date and time, we have to make sure that the format of the date-time we are trying to insert in a table matches the format of the date-time column of the database. Following are the type of date-time formats in SQL:
To learn more about the date-time format in SQL, click here. Conclusion
What is the formula to calculate age?Calculate age. Can Excel calculate age based on birthdate?Simply by subtracting the birth date from the current date. This conventional age formula can also be used in Excel. The first part of the formula (TODAY()-B2) returns the difference between the current date and date of birth is days, and then you divide that number by 365 to get the numbers of years.
How do I calculate my age manually by date of birth?The method of calculating age involves the comparison of a person's date of birth with the date on which the age needs to be calculated. The date of birth is subtracted from the given date, which gives the age of the person. Age = Given date - Date of birth.
How do you calculate age from birth year?How do I calculate someone's age in years?. Take the difference between the current year and their birth year: years = current year - birth year .. Take the difference between the current month and their birth month: months = current month - birth month .. |