w3resource logo

:

MySQL exercises

MySQL Date and Time - Exercises, Practice, Solution

Date and Time functions [21 exercises with solution]

1. Write a query to display the first day of the month (in datetime format) three months before the current month. Go to the editor
Sample current date : 2014-09-03
Expected result : 2014-06-01

Click me to see the solution

2. Write a query to display the last day of the month (in datetime format) three months before the current month. Go to the editor

Click me to see the solution

3. Write a query to get the distinct Mondays from hire_date in employees tables. Go to the editor

Sample table: employees


Click me to see the solution

4. Write a query to get the first day of the current year. Go to the editor

Click me to see the solution

5. Write a query to get the last day of the current year. Go to the editor

Click me to see the solution

6. Write a query to calculate the age in year. Go to the editor

Click me to see the solution

7. Write a query to get the current date in the following format. Go to the editor
Sample date : 2014-09-04
Output : September 4, 2014

Click me to see the solution

8. Write a query to get the current date in Thursday September 2014 format. Go to the editor
Thursday September 2014

Click me to see the solution

9. Write a query to extract the year from the current date. Go to the editor

Click me to see the solution

10. Write a query to get the DATE value from a given day (number in N). Go to the editor
Sample days: 730677
Output : 2000-07-11

Click me to see the solution

11. Write a query to get the first name and hire date from employees table where hire date between '1987-06-01' and '1987-07-30' Go to the editor

Sample table: employees


Click me to see the solution

12. Write a query to display the current date in the following format. Go to the editor
Sample output: Thursday 4th September 2014 00:00:00

Click me to see the solution

13. Write a query to display the current date in the following format. Go to the editor
Sample output: 05/09/2014

Click me to see the solution

14. Write a query to display the current date in the following format. Go to the editor
Sample output: 12:00 AM Sep 5, 2014

Click me to see the solution

15. Write a query to get the firstname, lastname who joined in the month of June. Go to the editor

Sample table: employees


Click me to see the solution

16. Write a query to get the years in which more than 10 employees joined. Go to the editor

Sample table: employees


Click me to see the solution

17. Write a query to get first name of employees who joined in 1987. Go to the editor

Sample table: employees


Click me to see the solution

18. Write a query to get department name, manager name, and salary of the manager for all managers whose experience is more than 5 years. Go to the editor

Sample table: departments


Click me to see the solution

19. Write a query to get employee ID, last name, and date of first salary of the employees. Go to the editor

Sample table: employees


Click me to see the solution

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

Sample table: employees


Click me to see the solution

21. Write a query to get the department ID, year, and number of employees joined. Go to the editor

Sample table : employees

Click me to see the solution

... More

MySQL Code Editor: