How to Get the Day of the Week in SQL

1. Introduction

Extracting the day of the week is a common requirement while working with SQL. Whether we’re generating reports, scheduling tasks, or analyzing trends, knowing the specific day can be crucial for effective data analysis.

In this quick tutorial, we’ll learn to get the day of the week from a date field using MySQL, MSSQL, and PostgreSQL.

We’ll demonstrate some practical applications using various tables in the University database.

2. MySQL

In MySQL, we have four different functions at our disposal. We’ll use the DAYOFWEEK(), DAYNAME(), WEEKDAY(), DATE_FORMAT() functions respectively.

2.1. Using the DAYOFWEEK() Function

To find which day of the week students most frequently registered for a course we can use the DAYOFWEEK() function on the Registration table. We’ll use an additional COUNT() function to count the number of registrations:

SELECT 
    DAYOFWEEK(reg_datetime) AS day_of_week,
    COUNT(*) AS registration_count
FROM University.Registration
GROUP BY 1
ORDER BY 2 DESC;
+-------------+--------------------+
| day_of_week | registration_count |
+-------------+--------------------+
|           3 |                 33 |
|           4 |                 33 |
|           2 |                 26 |
|           7 |                 18 |
|           5 |                 15 |
|           6 |                 13 |
|           1 |                  9 |
+-------------+--------------------+

The DAYOFWEEK() function returns the weekdays with numbers 1 being Sunday to 7 being Saturday.

2.2. Using the DAYNAME() Function

We can use the DAYNAME() function to get the name of the day directly. This time around we’ll use the Exam table to find on which day of the week most of the exams were held:

SELECT 
    DAYNAME(exam_date) AS day_of_week, 
    COUNT(*) AS exam_count
FROM Exam
GROUP BY 1
ORDER BY 2 DESC;
+-------------+------------+
| day_of_week | exam_count |
+-------------+------------+
| Tuesday     |         31 |
| Thursday    |         27 |
| Friday      |         24 |
| Wednesday   |         22 |
| Monday      |         18 |
| Saturday    |         16 |
| Sunday      |          5 |
+-------------+------------+

Column exam_date contains the dates when the exams are held and by using the COUNT() function alongside the DAYNAME() we can count on which day most of the exams are happening.

2.3. Using the WEEKDAY() Function

Next, we’ll use the WEEKDAY() function on the start_date column of the Faculty table. First, we’ll use the COUNT() function to count on which day the Faculty members started teaching and later group them together with the GROUP BY statement:

SELECT 
    WEEKDAY(start_date) AS weekday_index, 
    COUNT(*) AS start_count
FROM Faculty
GROUP BY 1
ORDER BY 2;
+---------------+-------------+
| weekday_index | start_count |
+---------------+-------------+
|             5 |           7 |
|             3 |           7 |
|             2 |           9 |
|             1 |          11 |
|             4 |          11 |
|             6 |          13 |
|             0 |          14 |
+---------------+-------------+

Like the DAYOFWEEK() function, the WEEKDAY() also yields a number where 0 = Monday, 1 = Tuesday, and so on.

2.4. Using the DATE_FORMAT() Function

This time around we’ll use the DATE_FORMAT() function on the Student table. Same as before we’ll use the COUNT() function to count how many students enrolled on a particular day of the week:

SELECT 
    DATE_FORMAT(enrollment_date, '%W') AS day_of_week, 
    COUNT(*) AS enrollment_count
FROM Student
GROUP BY 1
ORDER BY 2 DESC;
+-------------+------------------+
| day_of_week | enrollment_count |
+-------------+------------------+
| Wednesday   |               10 |
| Friday      |               10 |
| Saturday    |                5 |
+-------------+------------------+

Using %W with the DATE_FORMAT() will produce the full name of the day but using %a will return the abbreviated form of the weekdays.

3. PostgreSQL

In this section, we’ll check three ways to get the day of the week in PostgreSQL. First, we’ll use the TO_CHAR() function and then the EXTRACT() along with the DATE_PART() function.

3.1. Using the TO_CHAR() Function

In PostgreSQL, we can use the TO_CHAR() function to get the exact day name of the week. We’ll use both the Course and Registration tables this time to find the registration_count of a certain course – Introduction to Operating Systems per day of the week.

SELECT 
    TO_CHAR(reg_datetime, 'Day') AS day_of_week, 
    COUNT(*) AS registration_count
FROM Registration
WHERE course_id = (
	SELECT id 
	FROM Course 
	WHERE name = 'Introduction to Operating Systems')
GROUP BY 1
ORDER BY 2 DESC;
day_of_week | registration_count 
-------------+-------------------
 Wednesday   |                  5
 Friday      |                  1
 Saturday    |                  1
 Sunday      |                  1
 Tuesday     |                  1

First, we’ll extract the course_id by using a subquery and later find the registration day using the TO_CHAR() function.

3.2. Using the EXTRACT() Function

Let’s use the EXTRACT() function on the Student table to find which day of the week all the students were born. We’ll use the COUNT() function as well as the GROUP BY statement to count the birthdays and group them in their perspective dates:

SELECT 
    EXTRACT(DOW FROM birth_date) AS day_of_week, 
    COUNT(*) AS birthday_count
FROM Student
GROUP BY 1
ORDER BY 2 DESC;
 day_of_week | birthday_count
-------------+---------------
           1 |              7
           4 |              5
           3 |              5
           5 |              2
           6 |              2
           2 |              2
           0 |              2

The EXTRACT() function will fetch the day of the week in the form of a number (0 = Sunday, 1 = Monday)

3.3. Using the DATE_PART() Function

The DATE_PART() function in PostgreSQL is another way to extract specific parts of a date or timestamp, including the day of the week. The SQL query of this sub-section is similar to the last one:

SELECT 
    DATE_PART('dow', birth_date) AS day_of_week, 
    COUNT(*) AS birthday_count
FROM Student
GROUP BY 1
ORDER BY 2 DESC;

The DATE_PART() function produces the same result as the EXTRACT() function so for brevity, we won’t show the result here.

4. SQL Server

For SQL Server, we’ll look into two different ways to get the day of the week e.g. DATENAME() and DATEPART() functions.

4.1. Using the DATENAME() Function

In SQL Server, we can use the DATENAME() function to extract specific parts of a date i.e. the year, month, or day. Let’s use the start_date column of the faculty table in this case:

SELECT 
    DATENAME(weekday, start_date) AS day_of_week, 
    COUNT(*) AS start_count
FROM Faculty
GROUP BY DATENAME(weekday, start_date)
ORDER BY 2 DESC;

The concurrent use of the COUNT() and DATENAME() functions can give us the full name of the day.

4.2. Using the DATEPART() Function

Lastly, we’ll use the DATEPART() function on the birth_date column of the Student table. We’ll simultaneously use the DATEPART() and COUNT() functions along with the GROUP BY statement to produce the desired result:

SELECT 
    DATEPART(weekday, birth_date) AS day_of_week, 
    COUNT(*) AS start_count
FROM Student
GROUP BY DATEPART(weekday, birth_date)
ORDER BY 2 DESC;

The DATEPART() function returns the day number where 1 = Sunday and so on.

5. Conclusion

In this article, we investigated various functions to get the day of the week in MySQL, PostgreSQL, and SQL Server.

We used various functions available in each DBMS to achieve this goal:

• In MySQL, we used DAYOFWEEK(), DAYNAME(), WEEKDAY(), and DATE_FORMAT() functions
• In PostgreSQL, we looked into TO_CHAR(), EXTRACT() and DATE_PART() functions
• In SQL Server, we used DATENAME() and DATEPART() functions