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’
- 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)
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;
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
Get new content delivered directly to your inbox.