SQL Query How to get Datediff in MySQL

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.

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