How DAYOFWEEK() Works in MariaDB

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'