MySQL Date and Time Exercises: Query to get first name, hire date and experience of the employees

Last update on March 14 2024 11:22:21 (UTC/GMT +8 hours)

MySQL Date Time: Exercise-20 with Solution

Write a MySQL query to get first name, hire date and experience of the employees.

Sample table: employees

Code:

-- This SQL query retrieves the first name of employees, the current date, their hire date, and calculates their years of employment.

SELECT 
    FIRST_NAME, -- Selecting the 'FIRST_NAME' column from the 'employees' table.
SYSDATE(), -- Retrieving the current date using the SYSDATE() function.
    HIRE_DATE, -- Selecting the 'HIRE_DATE' column from the 'employees' table.
DATEDIFF(SYSDATE(), hire_date)/365 -- Calculating the difference in days between the current date and the hire date, then dividing by 365 to get years of employment.
FROM 
employees; -- Specifying the 'employees' table.

Explanation:

This SQL query retrieves the first name of employees, the current date, their hire date, and calculates their years of employment.
The SYSDATE() function retrieves the current date.
The DATEDIFF() function calculates the difference in days between the current date and the hire date.
The result of DATEDIFF() is divided by 365 to convert days into years, providing the years of employment.
The query returns a result set containing the first name of employees, the current date, their hire date, and their years of employment.

Relational Algebra Expression:

Relational Algebra Tree:

Sample Output:

 FIRST_NAME		SYSDATE()		HIRE_DATE		DATEDIFF( SYSDATE(), hire_date )/365
Steven		2017-08-30T09:46:50.000Z	1987-06-17T04:00:00.000Z	30.2247
Neena		2017-08-30T09:46:50.000Z	1987-06-18T04:00:00.000Z	30.2219
Lex		2017-08-30T09:46:50.000Z	1987-06-19T04:00:00.000Z	30.2192
Alexander	2017-08-30T09:46:50.000Z	1987-06-20T04:00:00.000Z	30.2164
Bruce		2017-08-30T09:46:50.000Z	1987-06-21T04:00:00.000Z	30.2137
David		2017-08-30T09:46:50.000Z	1987-06-22T04:00:00.000Z	30.211
Valli		2017-08-30T09:46:50.000Z	1987-06-23T04:00:00.000Z	30.2082
Diana		2017-08-30T09:46:50.000Z	1987-06-24T04:00:00.000Z	30.2055
Nancy		2017-08-30T09:46:50.000Z	1987-06-25T04:00:00.000Z	30.2027
Daniel		2017-08-30T09:46:50.000Z	1987-06-26T04:00:00.000Z	30.2
John		2017-08-30T09:46:50.000Z	1987-06-27T04:00:00.000Z	30.1973
Ismael		2017-08-30T09:46:50.000Z	1987-06-28T04:00:00.000Z	30.1945
Jose Manuel	2017-08-30T09:46:50.000Z	1987-06-29T04:00:00.000Z	30.1918
Luis		2017-08-30T09:46:50.000Z	1987-06-30T04:00:00.000Z	30.189
Den		2017-08-30T09:46:50.000Z	1987-07-01T04:00:00.000Z	30.1863
Alexander	2017-08-30T09:46:50.000Z	1987-07-02T04:00:00.000Z	30.1836
Shelli		2017-08-30T09:46:50.000Z	1987-07-03T04:00:00.000Z	30.1808
Sigal		2017-08-30T09:46:50.000Z	1987-07-04T04:00:00.000Z	30.1781
Guy		2017-08-30T09:46:50.000Z	1987-07-05T04:00:00.000Z	30.1753
Karen		2017-08-30T09:46:50.000Z	1987-07-06T04:00:00.000Z	30.1726
Matthew		2017-08-30T09:46:50.000Z	1987-07-07T04:00:00.000Z	30.1699
Adam		2017-08-30T09:46:50.000Z	1987-07-08T04:00:00.000Z	30.1671
Payam		2017-08-30T09:46:50.000Z	1987-07-09T04:00:00.000Z	30.1644
Shanta		2017-08-30T09:46:50.000Z	1987-07-10T04:00:00.000Z	30.1616
Kevin		2017-08-30T09:46:50.000Z	1987-07-11T04:00:00.000Z	30.1589
Julia		2017-08-30T09:46:50.000Z	1987-07-12T04:00:00.000Z	30.1562
Irene		2017-08-30T09:46:50.000Z	1987-07-13T04:00:00.000Z	30.1534
James		2017-08-30T09:46:50.000Z	1987-07-14T04:00:00.000Z	30.1507
Steven		2017-08-30T09:46:50.000Z	1987-07-15T04:00:00.000Z	30.1479
Laura		2017-08-30T09:46:50.000Z	1987-07-16T04:00:00.000Z	30.1452
Mozhe		2017-08-30T09:46:50.000Z	1987-07-17T04:00:00.000Z	30.1425
James		2017-08-30T09:46:50.000Z	1987-07-18T04:00:00.000Z	30.1397
TJ		2017-08-30T09:46:50.000Z	1987-07-19T04:00:00.000Z	30.137
Jason		2017-08-30T09:46:50.000Z	1987-07-20T04:00:00.000Z	30.1342
Michael		2017-08-30T09:46:50.000Z	1987-07-21T04:00:00.000Z	30.1315
Ki		2017-08-30T09:46:50.000Z	1987-07-22T04:00:00.000Z	30.1288
Hazel		2017-08-30T09:46:50.000Z	1987-07-23T04:00:00.000Z	30.126
Renske		2017-08-30T09:46:50.000Z	1987-07-24T04:00:00.000Z	30.1233
Stephen		2017-08-30T09:46:50.000Z	1987-07-25T04:00:00.000Z	30.1205
John		2017-08-30T09:46:50.000Z	1987-07-26T04:00:00.000Z	30.1178
Joshua		2017-08-30T09:46:50.000Z	1987-07-27T04:00:00.000Z	30.1151
Trenna		2017-08-30T09:46:50.000Z	1987-07-28T04:00:00.000Z	30.1123
Curtis		2017-08-30T09:46:50.000Z	1987-07-29T04:00:00.000Z	30.1096
Randall		2017-08-30T09:46:50.000Z	1987-07-30T04:00:00.000Z	30.1068
Peter		2017-08-30T09:46:50.000Z	1987-07-31T04:00:00.000Z	30.1041
John		2017-08-30T09:46:50.000Z	1987-08-01T04:00:00.000Z	30.1014
Karen		2017-08-30T09:46:50.000Z	1987-08-02T04:00:00.000Z	30.0986
Alberto		2017-08-30T09:46:50.000Z	1987-08-03T04:00:00.000Z	30.0959
Gerald		2017-08-30T09:46:50.000Z	1987-08-04T04:00:00.000Z	30.0932
Eleni		2017-08-30T09:46:50.000Z	1987-08-05T04:00:00.000Z	30.0904
Peter		2017-08-30T09:46:50.000Z	1987-08-06T04:00:00.000Z	30.0877
David		2017-08-30T09:46:50.000Z	1987-08-07T04:00:00.000Z	30.0849
Peter		2017-08-30T09:46:50.000Z	1987-08-08T04:00:00.000Z	30.0822
Christopher	2017-08-30T09:46:50.000Z	1987-08-09T04:00:00.000Z	30.0795
Nanette		2017-08-30T09:46:50.000Z	1987-08-10T04:00:00.000Z	30.0767
Oliver		2017-08-30T09:46:50.000Z	1987-08-11T04:00:00.000Z	30.074
Janette		2017-08-30T09:46:50.000Z	1987-08-12T04:00:00.000Z	30.0712
Patrick		2017-08-30T09:46:50.000Z	1987-08-13T04:00:00.000Z	30.0685
Allan		2017-08-30T09:46:50.000Z	1987-08-14T04:00:00.000Z	30.0658
Lindsey		2017-08-30T09:46:50.000Z	1987-08-15T04:00:00.000Z	30.063
Louise		2017-08-30T09:46:50.000Z	1987-08-16T04:00:00.000Z	30.0603
Sarath		2017-08-30T09:46:50.000Z	1987-08-17T04:00:00.000Z	30.0575
Clara		2017-08-30T09:46:50.000Z	1987-08-18T04:00:00.000Z	30.0548
Danielle	2017-08-30T09:46:50.000Z	1987-08-19T04:00:00.000Z	30.0521
Mattea		2017-08-30T09:46:50.000Z	1987-08-20T04:00:00.000Z	30.0493
David		2017-08-30T09:46:50.000Z	1987-08-21T04:00:00.000Z	30.0466
Sundar		2017-08-30T09:46:50.000Z	1987-08-22T04:00:00.000Z	30.0438
Amit		2017-08-30T09:46:50.000Z	1987-08-23T04:00:00.000Z	30.0411
Lisa		2017-08-30T09:46:50.000Z	1987-08-24T04:00:00.000Z	30.0384
Harrison	2017-08-30T09:46:50.000Z	1987-08-25T04:00:00.000Z	30.0356
Tayler		2017-08-30T09:46:50.000Z	1987-08-26T04:00:00.000Z	30.0329
William		2017-08-30T09:46:50.000Z	1987-08-27T04:00:00.000Z	30.0301
Elizabeth	2017-08-30T09:46:50.000Z	1987-08-28T04:00:00.000Z	30.0274
Sundita		2017-08-30T09:46:50.000Z	1987-08-29T04:00:00.000Z	30.0247
Ellen		2017-08-30T09:46:50.000Z	1987-08-30T04:00:00.000Z	30.0219
Alyssa		2017-08-30T09:46:50.000Z	1987-08-31T04:00:00.000Z	30.0192
Jonathon	2017-08-30T09:46:50.000Z	1987-09-01T04:00:00.000Z	30.0164
Jack		2017-08-30T09:46:50.000Z	1987-09-02T04:00:00.000Z	30.0137
Kimberely	2017-08-30T09:46:50.000Z	1987-09-03T04:00:00.000Z	30.011
Charles		2017-08-30T09:46:50.000Z	1987-09-04T04:00:00.000Z	30.0082
Winston		2017-08-30T09:46:50.000Z	1987-09-05T04:00:00.000Z	30.0055
Jean		2017-08-30T09:46:50.000Z	1987-09-06T04:00:00.000Z	30.0027
Martha		2017-08-30T09:46:50.000Z	1987-09-07T04:00:00.000Z	30
Girard		2017-08-30T09:46:50.000Z	1987-09-08T04:00:00.000Z	29.9973
Nandita		2017-08-30T09:46:50.000Z	1987-09-09T04:00:00.000Z	29.9945
Alexis		2017-08-30T09:46:50.000Z	1987-09-10T04:00:00.000Z	29.9918
Julia		2017-08-30T09:46:50.000Z	1987-09-11T04:00:00.000Z	29.989
Anthony		2017-08-30T09:46:50.000Z	1987-09-12T04:00:00.000Z	29.9863
Kelly		2017-08-30T09:46:50.000Z	1987-09-13T04:00:00.000Z	29.9836
Jennifer	2017-08-30T09:46:50.000Z	1987-09-14T04:00:00.000Z	29.9808
Timothy		2017-08-30T09:46:50.000Z	1987-09-15T04:00:00.000Z	29.9781
Randall		2017-08-30T09:46:50.000Z	1987-09-16T04:00:00.000Z	29.9753
Sarah		2017-08-30T09:46:50.000Z	1987-09-17T04:00:00.000Z	29.9726
Britney		2017-08-30T09:46:50.000Z	1987-09-18T04:00:00.000Z	29.9699
Samuel		2017-08-30T09:46:50.000Z	1987-09-19T04:00:00.000Z	29.9671
Vance		2017-08-30T09:46:50.000Z	1987-09-20T04:00:00.000Z	29.9644
Alana		2017-08-30T09:46:50.000Z	1987-09-21T04:00:00.000Z	29.9616
Kevin		2017-08-30T09:46:50.000Z	1987-09-22T04:00:00.000Z	29.9589
Donald		2017-08-30T09:46:50.000Z	1987-09-23T04:00:00.000Z	29.9562
Douglas		2017-08-30T09:46:50.000Z	1987-09-24T04:00:00.000Z	29.9534
Jennifer	2017-08-30T09:46:50.000Z	1987-09-25T04:00:00.000Z	29.9507
Michael		2017-08-30T09:46:50.000Z	1987-09-26T04:00:00.000Z	29.9479
Pat		2017-08-30T09:46:50.000Z	1987-09-27T04:00:00.000Z	29.9452
Susan		2017-08-30T09:46:50.000Z	1987-09-28T04:00:00.000Z	29.9425
Hermann		2017-08-30T09:46:50.000Z	1987-09-29T04:00:00.000Z	29.9397
Shelley		2017-08-30T09:46:50.000Z	1987-09-30T04:00:00.000Z	29.937
William		2017-08-30T09:46:50.000Z	1987-10-01T04:00:00.000Z	29.9342

Pictorial Presentation of the above query:

Pictorial: Query to get first name of employees who joined in 1987

MySQL Code Editor:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous:Write a MySQL query to get employee ID, last name, and date of first salary of the employees.
Next:Write a MySQL query to get the department ID, year, and number of employees joined.