SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries What are single row and multiple row subqueries?

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL

How to calculate age from Date of Birth in SQL?

To convert the date of birth to age in SQL, we can use the DATEDIFF function. The DATEDIFF function calculates the difference between two dates, expressed in a specified date part, such as years, months, or days.

To convert date of birth to age, you would subtract the date of birth from the current date or a specified date, and express the difference in years.

Here is an example of how to convert date of birth to age in SQL using the DATEDIFF function:

SELECT name, DATEDIFF(YEAR, date_of_birth, GETDATE()) AS age
FROM employees;

In this example, name and date_of_birth are columns in the employees table, and GETDATE() returns the current date.

The DATEDIFF function calculates the difference in years between the date_of_birth and the current date, and the result of the calculation is stored in a new column age. This query returns the name and age of each employee in the employees table.

Let’s take an example to understand it in detail:

Example 1:

Here is an example of how you could calculate age from date of birth in SQL using a sample table:

CREATE TABLE people (
id INT PRIMARY KEY,
name VARCHAR(50),
date_of_birth DATE
);
INSERT INTO people (id, name, date_of_birth)
VALUES (1, 'John Doe', '1980-01-01'),
(2, 'Jane Doe', '1985-05-01'),
    (3, 'Jim Smith', '1990-08-01');

This creates a table named people with three rows of sample data. To calculate the age of each person, you can use the following query:

SELECT name, DATEDIFF(YEAR, date_of_birth, GETDATE()) AS age
FROM people;

This query returns the name and age of each person in the people table. The DATEDIFF function calculates the difference in years between the date_of_birth and the current date, returned by GETDATE(). The result of the calculation is stored in a new column age.

Output:

Here is what the result of the above query would look like:

NameAge
John Doe  43  
Jane Doe  38  
Jim Smith33

Example 2:

Here is another example of how you could calculate age from date of birth in SQL using a sample table:

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
date_of_birth DATE,
hire_date DATE
);
INSERT INTO employees (id, name, date_of_birth, hire_date)
VALUES (1, 'Alice', '1980-01-01', '2010-01-01'),
       (2, 'Bob', '1985-05-01', '2015-01-01'),
       (3, 'Charlie', '1990-08-01', '2020-01-01');

This creates a table named employees with three rows of sample data. To calculate the age of each employee as of their hire date, you can use the following query:

SELECT name, DATEDIFF(YEAR, date_of_birth, hire_date) AS age_at_hire
FROM employees;

This query returns the name and age of each employee in the employees table as of their hire date. The DATEDIFF function calculates the difference in years between the date_of_birth and the hire_date. The result of the calculation is stored in a new column age_at_hire.

Here is what the result of the above query would look like:

NameAge
Alice30
Bob25
Charlie20

Different methods to calculate age from the date of birth

Here is another method to calculate age from date of birth in SQL:

SELECT name, 
FLOOR(DATEDIFF(day, date_of_birth, GETDATE()) / 365.25) AS age
FROM employees;

In this method, FLOOR rounds down the result of the calculation to the nearest whole number. The calculation itself uses the DATEDIFF function to find the difference in days between the date_of_birth and the current date, returned by GETDATE(). The result is divided by 365.25, which is the average number of days in a year including leap years.

This method provides a more accurate representation of age, accounting for leap years, and gives the same result as the previous method. The result of this query would be the same as the previous example, with the names and ages of each employee in the employees table.

Example 3:

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
date_of_birth DATE,
hire_date DATE
);
INSERT INTO employees (id, name, date_of_birth, hire_date)
VALUES (1, 'Akash', '1980-01-01', '2010-01-01'),
       (2, 'Bunty', '1985-05-01', '2015-01-01'),
       (3, 'Chaplin', '1990-08-01', '2020-01-01');

As we all know that this table is created with three sample row of data. If we want to calculate the age of each employee as of the current date, then we can use the following queries:

SELECT name, 
FLOOR(DATEDIFF(day, date_of_birth, GETDATE()) / 365.25) AS age
FROM employees;

The FLOOR function rounds down the result of the calculation to the nearest whole number. The calculation itself uses the DATEDIFF function to find the difference in days between the date_of_birth and the current date, returned by GETDATE().

The result is divided by 365.25, which is the average number of days in a year including leap years.

Here's what the result of the above query would look like, assuming the current date is 2023-02-01:

NameAge
Akash43
Bunty38
Chaplin33