When using MySQL, you can use the MAKEDATE() function to return a date from the year and day-of-year parts.

In other words, you provide two arguments; one being the year, and the other being the day-of-year. The MAKEDATE() function will then return the date value based on those two arguments.

Syntax

The syntax goes like this:

MAKEDATE(year,dayofyear)

Where year is the year part, and dayofyear is the day-of-year part.

Example 1 – Basic Usage

Here’s an example to demonstrate.

SELECT MAKEDATE(2021,10);

Result:

+-------------------+
| MAKEDATE(2021,10) |
+-------------------+
| 2021-01-10        |
+-------------------+

So because 10 means the 10th day of the year, this means that it’s the 10th of January.

Example 2 – A Larger Day-of-Year Value

Here’s an example that uses a larger day-of-year value.

SELECT MAKEDATE(2021,350);

Result:

+--------------------+
| MAKEDATE(2021,350) |
+--------------------+
| 2021-12-16         |
+--------------------+

So in this case, the 350th day of the year results in the 16th of December.

You can also use values larger than 365 (or 366 for leap years). If you do this, the result will flick over to a new calendar year as required.

SELECT 
    MAKEDATE(2021,500),
    MAKEDATE(2021,5000);

Result:

+--------------------+---------------------+
| MAKEDATE(2021,500) | MAKEDATE(2021,5000) |
+--------------------+---------------------+
| 2022-05-15         | 2034-09-09          |
+--------------------+---------------------+

Example 3 – Leap Years

Be mindful of leap years when using this function.

SELECT 
    MAKEDATE(2020,350),
    MAKEDATE(2021,350);

Result:

+--------------------+--------------------+
| MAKEDATE(2020,350) | MAKEDATE(2021,350) |
+--------------------+--------------------+
| 2020-12-15         | 2021-12-16         |
+--------------------+--------------------+

In this case, 2020 is a leap year. And because February has an extra day during leap years, this affects the outcome of the remaining day-of-year values.

Example 4 – Zero Values

If you provide a zero value, the result is NULL.

SELECT MAKEDATE(2020,0);

Result:

+------------------+
| MAKEDATE(2020,0) |
+------------------+
| NULL             |
+------------------+