In MariaDB, DAYOFYEAR()
is a built-in date and time function that returns the day of the year from a given date.
It accepts one argument, which is the date you want to extract the day of the year from.
It returns the day as a number in the range 1
to 366
.
Syntax
The syntax goes like this:
DAYOFYEAR(date)
Where date
is the date to get the day of the year from.
Example
Here’s an example:
SELECT DAYOFYEAR('2030-12-25');
Result:
+-------------------------+ | DAYOFYEAR('2030-12-25') | +-------------------------+ | 359 | +-------------------------+
Datetime Values
It also works with datetime values:
SELECT DAYOFYEAR('2030-02-01 10:30:45');
Result:
+----------------------------------+ | DAYOFYEAR('2030-02-01 10:30:45') | +----------------------------------+ | 32 | +----------------------------------+
Zero Days
Zero days result in null
.
Example:
SELECT DAYOFYEAR('2030-00-00');
Result:
+-------------------------+ | DAYOFYEAR('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 DAYOFYEAR(20301125);
Result:
+---------------------+ | DAYOFYEAR(20301125) | +---------------------+ | 329 | +---------------------+
Or even the following (which uses a two-digit year):
SELECT DAYOFYEAR(301125);
Result:
+-------------------+ | DAYOFYEAR(301125) | +-------------------+ | 329 | +-------------------+
But it must make sense as a date. Here’s what happens if I increase the day part to an invalid day:
SELECT DAYOFYEAR(20300135);
Result:
+---------------------+ | DAYOFYEAR(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
DAYOFYEAR('2030/06/25'),
DAYOFYEAR('2030,06,25'),
DAYOFYEAR('2030:06:25'),
DAYOFYEAR('2030;06!25');
Result (using vertical output):
DAYOFYEAR('2030/06/25'): 176 DAYOFYEAR('2030,06,25'): 176 DAYOFYEAR('2030:06:25'): 176 DAYOFYEAR('2030;06!25'): 176
Current Date
We can pass NOW()
as the date argument to use the current date:
SELECT
NOW(),
DAYOFYEAR(NOW());
Result:
+---------------------+------------------+ | NOW() | DAYOFYEAR(NOW()) | +---------------------+------------------+ | 2021-05-16 09:05:57 | 136 | +---------------------+------------------+
Invalid Arguments
When passed an invalid argument, DAYOFYEAR()
returns null
:
SELECT DAYOFYEAR('Friday');
Result:
+---------------------+ | DAYOFYEAR('Friday') | +---------------------+ | NULL | +---------------------+
Missing Argument
Calling DAYOFYEAR()
with the wrong number of arguments, or without passing any arguments, results in an error:
SELECT DAYOFYEAR();
Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'DAYOFYEAR'
And another example:
SELECT DAYOFYEAR('2030-01-25', '2045-05-08');
Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'DAYOFYEAR'