SQL ORDER BY DATE

• ORDER BY is a clause in SQL which is used with SELECT query to fetch the records in ascending or descending order from a table.
• Just like we sort the integer and the string values stored in the column of the tables, similarly, we can sort the dates stored in the SQL table's column.
• All the records will be, by default, sorted in the ascending order. To sort the records in descending order, the DESC keyword is used.

Let us see few practical examples to understand this concept more clearly. We will use the MySQL database for writing all the queries.

Consider we have created a table named as employees in MySQL database with the following data:

Example 1:

Write a query to display all the details of employees arranged in ascending order of their date of birth.

Query:

Since we wanted to sort the records in the ascending order of the date of birth of employees, so we have applied the ORDER BY clause on the column 'DOB'.

You will get the following output:

The results obtained from the above query show that the records are displayed according to the ascending order of the DOB.

Example 2:

Write a query to display all the details of employees arranged in descending order of their joining dates.

Query:

Since we wanted to sort the records in the descending order of the joining date of employees, so we have applied the ORDER BY clause with the DESC keyword on the column 'Joining_Date'.

You will get the following output:

The results obtained from the above query show that the records are displayed according to the descending order of the joining dates.

Example 3:

Write a query to display all the details of employees arranged in ascending order of their year of birth.

Query:

Since we wanted to sort the records in the ascending order of the birth year of employees, so we have applied the ORDER BY clause. DATE_FORMAT () function is applied on the column DOB with the parameter '%Y' to extract only the year from 'DOB'.

You will get the following output:

The results obtained from the above query show that the records are displayed according to the ascending order of the employee's year of birth.

Example 4:

Write a query to display all the details of employees arranged in descending order of their hour of birth.

Query:

Since we wanted to sort the records in the descending order of the birth hour of employees, so we have applied the ORDER BY clause with the DESC keyword. DATE_FORMAT () function is applied on the column DOB with the parameter '%H' to extract only the hour of birth from the column'DOB'.

You will get the following output:

The results obtained from the above query show that the records are displayed according to the descending order of the employee's hour of birth.

Example 5:

Write a query to display all the details of employees arranged in ascending order of their year of joining.

Query:

Since we wanted to sort the records in the ascending order of the joining year of employees, so we have applied the ORDER BY clause. DATE_FORMAT () function is applied on the column Joining_Date with the parameter '%Y' to extract only the year from 'Joining_Date'.

You will get the following output:

The results obtained from the above query show that the records are displayed according to the ascending order of the joining year of employees.

Example 6:

Write a query to display all the details of employees arranged in descending order of the joining day of employees.

Query:

Since we wanted to sort the records in the descending order of the joining day of employees, so we have applied the ORDER BY clause with the DESC keyword. DAY () function is applied on the column 'Joining_Date' to extract only the day of joining from Joining_Date.

You will get the following output:

The results obtained from the above query show that the records are displayed according to the descending order of the joining day of employees.

Example 7:

Write a query to display all the details of employees arranged in ascending order of the birth day of employees.

Query:

Since we wanted to sort the records in the ascending order of the day of birth of employees, so we have applied the ORDER BY clause. DAY () function is applied on the column 'DOB' to extract only the day of birth from DOB.

You will get the following output:

The results obtained from the above query show that the records are displayed according to the ascending order of the employee's day of birth.

Example 8:

Write a query to display all the details of employees arranged in ascending order of the employee's birth month.

Query:

The results obtained from the above query show that the records are displayed according to the ascending order of the employee's birth month.

Example 9:

Write a query to display all the details of employees arranged in ascending order of the employee's month of joining.

Query:

Since we wanted to sort the records in the ascending order of the joining month of employees, so we have applied the ORDER BY clause. MONTH () function is applied on the column 'Joining_Date' to extract only the joining month from Joining_Date.

You will get the following output:

The results obtained from the above query show that the records are displayed according to the ascending order of the employee's month of joining.