How DAYOFYEAR() Works in MariaDB

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'