MySQL MAKEDATE() Function

MySQL MAKEDATE() function; In this tutorial, i am going to show you MySQL MAKEDATE() function with the help of examples.

The MySQL makedate() function is used to create and return a date based on a year and a number of days value. The number of days must be greater than 0 otherwise it returns a NULL value.

Syntax of MySQL MAKEDATE() Function

The syntax of makedate() function is:

MAKEDATE(year,dayofyear)

Here, the year is part of the year, and the second one is dayofyear return the day-of-year part.

Example 1 – MySQL MAKEDATE() Function

Let’s take example using the MySQL makedate() function; as follows:

SELECT MAKEDATE(2020,5);

Output-1

+-------------------+
| MAKEDATE(2020,5)  |
+-------------------+
|  2020-01-05       |
+-------------------+

Note: Here, 5 means the 5th days of the year, this means that it’s the 5th of January.

Example 2 – MySQL MAKEDATE() Function

Let’s take another example of this function with larger day-of-year value; as follows:

SELECT MAKEDATE(2026,300);

Output-2

+--------------------+
| MAKEDATE(2026,300) |
+--------------------+
| 2026-10-27         |
+--------------------+

Here, 300th day of the year is return the 27th of October

Example 3 – MySQL MAKEDATE() Function

Let’s take an example using makedate() function with leap with year; as follows:

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

Output-3

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

Example 4 – MySQL MAKEDATE() Function

Be careful for leap years while using this function.

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

Output-4

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

In this case, 2020 is a leap year. And because Leap has an extra day during leap years, so it affects the result of the remaining years’ values.

Example 5 – MySQL MAKEDATE() with CURDATE() Function

Let’s take the last example with CURDATE() and YEAR(). Using CURDATE () we get the current date, we will get YEAR using YEAR(). Here is a MySQL query with all these works.

SELECT MAKEDATE(YEAR(CURDATE()),DAYOFYEAR(CURDATE()))

Output-5

+-------------------------------------------------+
|  MAKEDATE(YEAR(CURDATE()),DAYOFYEAR(CURDATE())) |
+-------------------------------------------------+
|          2019-07-20                             |
+-------------------------------------------------+

Conclusion

MySQL MAKEDATE() Function tutorial, you have learned how to use MySQL MAKEDATE() function with various examples.

MySQL MAKEDATE() Function