Here’s MySQL query shows the datediff between two dates using the DATEDIFF() function. In the following example, first, get Days, then derive to years.
Note: This is MySQL/Python Interview question asked in a recent interview.
Year in MySQL: How to get it from Date
At first, a table of ‘my_table’ was created, and inserted some data into it.
Assumptions:
- A year means 365 days
- The function DATEDIFF() first gets days, and dividing it with ‘365’ will give 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 /or not
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
Getting 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
You must be logged in to post a comment.