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:

ID	Name	Salary	Joining_Date	DOB
1	Rohit More	50000	2020-02-08	1991-01-28 18:06:08
2	Kunal Mohite	34000	2021-01-01	1990-05-15 19:10:00
3	Saurabh Jha	61000	2015-05-01	1983-02-20 12:18:45
4	Anant Desai	59000	2018-08-27	1978-06-29 15:45:13
5	Krishna Sharma	48000	2010-10-23	1999-03-21 02:14:56
6	Bhavesh Jain	37000	2021-07-03	1998-08-02 13:00:01

Example 1:

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

Query:

mysql> SELECT *FROM employees ORDER BY DOB;

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:

ID	Name	Salary	Joining_Date	DOB
4	Anant Desai	59000	2018-08-27	1978-06-29 15:45:13
3	Saurabh Jha	61000	2015-05-01	1983-02-20 12:18:45
2	Kunal Mohite	34000	2021-01-01	1990-05-15 19:10:00
1	Rohit More	50000	2020-02-08	1991-01-28 18:06:08
6	Bhavesh Jain	37000	2021-07-03	1998-08-02 13:00:01
5	Krishna Sharma	48000	2010-10-23	1999-03-21 02:14:56

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:

mysql> SELECT *FROM employees ORDER BY Joining_Date DESC;

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:

ID	Name	Salary	Joining_Date	DOB
6	Bhavesh Jain	37000	2021-07-03	1998-08-02 13:00:01
2	Kunal Mohite	34000	2021-01-01	1990-05-15 19:10:00
1	Rohit More	50000	2020-02-08	1991-01-28 18:06:08
4	Anant Desai	59000	2018-08-27	1978-06-29 15:45:13
3	Saurabh Jha	61000	2015-05-01	1983-02-20 12:18:45
5	Krishna Sharma	48000	2010-10-23	1999-03-21 02:14:56

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:

mysql> SELECT ID, Name, Salary, Joining_Date, DATE_FORMAT (DOB, '%Y') AS Year_of_Birth FROM employees ORDER BY DATE_FORMAT (DOB, '%Y');

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:

ID	Name	Salary	Joining_Date	Year_Of_Birth
4	Anant Desai	59000	2018-08-27	1978
3	Saurabh Jha	61000	2015-05-01	1983
2	Kunal Mohite	34000	2021-01-01	1990
1	Rohit More	50000	2020-02-08	1991
6	Bhavesh Jain	37000	2021-07-03	1998
5	Krishna Sharma	48000	2010-10-23	1999

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:

mysql> SELECT ID, Name, Salary, Joining_Date, DATE_FORMAT (DOB, '%H') AS Hour_Of_Birth FROM employees ORDER BY DATE_FORMAT (DOB, '%H') DESC;

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:

ID	Name	Salary	Joining_Date	Hour_Of_Birth
2	Kunal Mohite	34000	2021-01-01	19
1	Rohit More	50000	2020-02-08	18
4	Anant Desai	59000	2018-08-27	15
6	Bhavesh Jain	37000	2021-07-03	13
3	Saurabh Jha	61000	2015-05-01	12
5	Krishna Sharma	48000	2010-10-23	02

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:

mysql> SELECT ID, Name, Salary, DOB, DATE_FORMAT (Joining_Date, '%Y') AS Year_Of_Joining FROM employees ORDER BY DATE_FORMAT (Joining_Date, '%Y');

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:

ID	Name	Salary	Year_Of_Joining	DOB
5	Krishna Sharma	48000	2010	1999-03-21 02:14:56
3	Saurabh Jha	61000	2015	1983-02-20 12:18:45
4	Anant Desai	59000	2018	1978-06-29 15:45:13
1	Rohit More	50000	2020	1991-01-28 18:06:08
2	Kunal Mohite	34000	2021	1990-05-15 19:10:00
6	Bhavesh Jain	37000	2021	1998-08-02 13:00:01

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:

mysql> SELECT ID, Name, Salary, DAY (Joining_Date) AS Day_Of_Joining, DOB FROM employees ORDER BY DAY (Joining_Date) DESC;

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:

ID	Name	Salary	Day_Of_Joining	DOB
4	Anant Desai	59000	27	1978-06-29 15:45:13
5	Krishna Sharma	48000	23	1999-03-21 02:14:56
1	Rohit More	50000	8	1991-01-28 18:06:08
6	Bhavesh Jain	37000	3	1998-08-02 13:00:01
2	Kunal Mohite	34000	1	1990-05-15 19:10:00
3	Saurabh Jha	61000	1	1983-02-20 12:18:45

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:

mysql> SELECT ID, Name, Salary, Joining_Date, DAY (DOB) AS Day_Of_Birth FROM employees ORDER BY DAY (DOB);

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:

ID	Name	Salary	Year_Of_Joining	Day_Of_Birth
6	Bhavesh Jain	37000	2021-07-03	2
2	Kunal Mohite	34000	2021-01-01	15
3	Saurabh Jha	61000	2015-05-01	20
5	Krishna Sharma	48000	2010-10-23	21
1	Rohit More	50000	2020-02-08	28
4	Anant Desai	59000	2018-08-27	29

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:

mysql> SELECT ID, Name, Salary, Joining_Date, DOB FROM employees ORDER BY MONTH (DOB);

ID	Name	Salary	Joining_Date	DOB
1	Rohit More	50000	2020-02-08	1991-01-28 18:06:08
3	Saurabh Jha	61000	2015-05-01	1983-02-20 12:18:45
5	Krishna Sharma	48000	2010-10-23	1999-03-21 02:14:56
2	Kunal Mohite	34000	2021-01-01	1990-05-15 19:10:00
4	Anant Desai	59000	2018-08-27	1978-06-29 15:45:13
6	Bhavesh Jain	37000	2021-07-03	1998-08-02 13:00:01

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:

mysql> SELECT ID, Name, Salary, Joining_Date, DOB FROM employees ORDER BY MONTH (Joining_Date);

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:

ID	Name	Salary	Joining_Date	DOB
2	Kunal Mohite	34000	2021-01-01	1990-05-15 19:10:00
1	Rohit More	50000	2020-02-08	1991-01-28 18:06:08
3	Saurabh Jha	61000	2015-05-01	1983-02-20 12:18:45
6	Bhavesh Jain	37000	2021-07-03	1998-08-02 13:00:01
4	Anant Desai	59000	2018-08-27	1978-06-29 15:45:13
5	Krishna Sharma	48000	2010-10-23	1999-03-21 02:14:56

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.

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz

SQL ORDER BY DATE

Comment / Reply From

You May Also Like

SQL COUNT DISTINCT

What is Race Condition

Trigger in SQL

How to Use ORDER BY in SQL

How to Use GROUP BY in SQL

SQL Arithmetic Operators

SQL Languages

How to use Auto-Increment in SQL

Popular Post

PHP Tutorial

NET Framework

Python Tutorial

Language

PHP Tutorial

NET Framework

Python Tutorial

Newsletter