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