In MariaDB, DAYOFWEEK()
is a built-in date and time function that returns the day of the week from a given date.
It accepts one argument, which is the date you want to extract the day of the week from.
It returns the day as a number. The index numbering adheres to the ODBC standard (1
= Sunday, 2
= Monday, etc). This is in contrast to WEEKDAY()
, where 0
= Monday, 1
= Tuesday, etc.
Syntax
The syntax goes like this:
DAYOFWEEK(date)
Where date
is the date to get the day from.
Example
Here’s an example:
SELECT DAYOFWEEK('2030-01-25');
Result:
+-------------------------+ | DAYOFWEEK('2030-01-25') | +-------------------------+ | 6 | +-------------------------+
Compared with the Day Name
Here’s another one, alongside DAYNAME()
to return the day name:
SELECT
DAYOFWEEK('2030-01-20'),
DAYNAME('2030-01-20');
Result:
+-------------------------+-----------------------+ | DAYOFWEEK('2030-01-20') | DAYNAME('2030-01-20') | +-------------------------+-----------------------+ | 1 | Sunday | +-------------------------+-----------------------+
As mentioned, the index numbering starts at 1 for Sunday.
Here’s Saturday:
SELECT
DAYOFWEEK('2030-01-19'),
DAYNAME('2030-01-19');
Result:
+-------------------------+-----------------------+ | DAYOFWEEK('2030-01-19') | DAYNAME('2030-01-19') | +-------------------------+-----------------------+ | 7 | Saturday | +-------------------------+-----------------------+
Datetime Values
It also works with datetime values:
SELECT DAYOFWEEK('2030-01-24 10:30:45');
Result:
+----------------------------------+ | DAYOFWEEK('2030-01-24 10:30:45') | +----------------------------------+ | 5 | +----------------------------------+
Zero Days
Zero days result in null
.
Example:
SELECT DAYOFWEEK('2030-00-00');
Result:
+-------------------------+ | DAYOFWEEK('2030-00-00') | +-------------------------+ | NULL | +-------------------------+
Numeric Dates
It’s also possible to pass dates as a number, as long as it makes sense as a date.
Example
SELECT DAYOFWEEK(20300125);
Result:
+---------------------+ | DAYOFWEEK(20300125) | +---------------------+ | 6 | +---------------------+
Or even the following (which uses a two-digit year):
SELECT DAYOFWEEK(300125);
Result:
+-------------------+ | DAYOFWEEK(300125) | +-------------------+ | 6 | +-------------------+
But it must make sense as a date. Here’s what happens if I increase the day part to an invalid day:
SELECT DAYOFWEEK(20300135);
Result:
+---------------------+ | DAYOFWEEK(20300135) | +---------------------+ | NULL | +---------------------+
Other Delimiters
You can use other delimiters for the date. MariaDB is quite forgiving when it comes to delimiters on dates. Here are some valid examples:
SELECT
DAYOFWEEK('2030/01/25'),
DAYOFWEEK('2030,01,25'),
DAYOFWEEK('2030:01:25'),
DAYOFWEEK('2030;01!25');
Result (using vertical output):
DAYOFWEEK('2030/01/25'): 6 DAYOFWEEK('2030,01,25'): 6 DAYOFWEEK('2030:01:25'): 6 DAYOFWEEK('2030;01!25'): 6
Current Date
We can pass NOW()
as the date argument to use the current date:
SELECT
NOW(),
DAYOFWEEK(NOW());
Result:
+---------------------+------------------+ | NOW() | DAYOFWEEK(NOW()) | +---------------------+------------------+ | 2021-05-15 09:17:44 | 7 | +---------------------+------------------+
Invalid Arguments
When passed an invalid argument, DAYOFWEEK()
returns null
:
SELECT DAYOFWEEK('Homer');
Result:
+--------------------+ | DAYOFWEEK('Homer') | +--------------------+ | NULL | +--------------------+
Missing Argument
Calling DAYOFWEEK()
with the wrong number of arguments, or without passing any arguments, results in an error:
SELECT DAYOFWEEK();
Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'DAYOFWEEK'
And another example:
SELECT DAYOFWEEK('2030-01-25', '2045-05-08');
Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'DAYOFWEEK'