How to Derive Year from DATEs Useful MySQL Query

Here’s SQL to derive years using the DATEDIFF() function in MySQL. First, you will get the Days. Then you can derive years. Below is the example for your quick reference.

Note: This is MySQL/Python Interview question asked in a recent interview.

MySQL DATEDIFF derive Year

The name of the table that I’ve used for this SQL query is ‘my_table’

Assumptions:

• A year means 365 days
• The function DATEDIFF(), it first gets days. By dividing it with ‘365’, you will get years.

SQL Query to Create Table

create table my_table (name char(20), join_date date);

SQL Query to Insert Values

insert into my_table values ('srini', '1990-01-01');
insert into my_table values ('rama', '1985-01-01');
insert into my_table values ('krishna', '2010-01-01');

SQL Query to Check Values Inserted

select * from my_table;

SQL Query to get Names of those derived-year GT THAN 30 (greater than 30)

select name
from my_table
where datediff('2020-01-01', join_date)/365 > 30;

Th Query Output

name
srini
rama

How to Show Derived YEAR

select datediff('2020-01-01', join_date)/365 as derive_date
from my_table;

Result

The derive_date gives you Days. You need to convert it to ‘Years’. Then this is useful to use in SQL queries.

derive_date
===========
30.0192
35.0219
10.0055

Related Posts