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.
name | joining_date |
---|---|
srini | 1990-01-01 |
rama | 1985-01-01 |
krishna | 2010-01-01 |
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
Authentic References
Get new content delivered directly to your inbox.