MySQL MAKEDATE() Function with Examples

MySQL MAKEDATE() function; In this tutorial, we will learn MySQL MAKEDATE() function with the help of examples.

And as well as, will take a list of examples of mysql makedate function.

MySQL MAKEDATE() Function

In Mysql, The MAKEDATE() function to return date from the year and day-of-year parts.

Note: In other words, you will pass the two parameters in this function; one of the year, and the other of the day-of-year. The MAKEDATE() function will then return the date value based on those two parameters.

Syntax

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

Let’s take the first example for a better demonstration of makedate() of MySQL is:

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

Let’s take another example of this function with larger day-of-year value.

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

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

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

Output-3

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

Example-4 With Leap Years

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 CURDATE() | YEAR()

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

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

Subscribe to Blog via Email