Storing and working with dates in the database is a common use case. You’re storing data and want to keep a reference of the creation date or when an update happens. MySQL comes with dozens of date and time functions. It facilitates your work when filtering or grouping rows by dates.
This tutorial shows you how to translate a date to a related weekday in MySQL.
MySQL Series Overview
Get the Weekday From a Date In MySQL
MySQL includes a comprehensive set of date and time functions. If you’re looking through the list, you’ll find two functions translating a date to a weekday: WEEKDAY and DAYOFWEEK. Both functions return a weekday index for a given date:
SELECT WEEKDAY('2024-12-25');
-- 2
SELECT DAYOFWEEK('2024-12-25');
-- 4
As you can see, both functions return a different index number. That’s because WEEKDAY and DAYOFWEEK start counting on different days (Monday vs. Sunday) and have different starting numbers (0 vs. 1).
WEEKDAY vs DAYOFWEEK in MySQL
Both functions, WEEKDAY and DAYOFWEEK, return a weekday index for a given date. The difference between both functions is the way they map values:
WEEKDAY: 0 = Monday, 1 = Tuesday, …, 6 = SundayDAYOFWEEK: 1 = Sunday, 2 = Monday, …, 7 = Saturday
WEEKDAY and DAYOFWEEK, return NULL when the input date is NULL.
Translate Weekday Number to Name Using MySQL’s ELT Function
The translation of a date to a weekday index can be the first step. Your next step can be the translation of the weekday index to a human-readable format. It’s hard to memorize the weekday indexes and their actual days. Especially when MySQL has two weekday functions with different outputs and different ways of counting.
You can use MySQL’s ELT string function to make the weekday index human-readable. ELT returns the nth element from a list of strings. You may combine the WEEKDAY and ELT functions and represent a weekday by their name:
SELECT
ELT(WEEKDAY('2024-12-25') + 1, 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun') as weekday;
// "Wed" - for date `2024-12-25`
The WEEKDAY function is zero-based and ELT is one-based. You need to add the + 1 to the weekday index to create a match between the index and the list of weekday names.
That’s it!