MySQL MAKEDATE() Function

The MAKEDATE() function creates and returns a date based on a specified year and a number representing the day of the year.

Definition and Usage

• Purpose: To generate a date using a year and the day of the year.
• Day of Year: Day values start from 1 (January 1) and can go up to 366 in leap years.

Syntax

MAKEDATE(year, day)

Parameter Values

Technical Details

• Introduced in: MySQL 4.0 and later.
• Return Type: A DATE value in the format YYYY-MM-DD.

Examples

Example 1: Creating a Date for the 3rd Day of 2017

SELECT MAKEDATE(2017, 3);

Result: 2017-01-03

Example 2: Creating a Date for the 175th Day of 2017

SELECT MAKEDATE(2017, 175);

Result: 2017-06-24

Example 3: Using 366 for a Leap Year

SELECT MAKEDATE(2016, 366);

Result: 2016-12-31
(Note: 2016 is a leap year.)

Example 4: Non-Leap Year with 366

SELECT MAKEDATE(2017, 366);

Result: NULL
(Note: 2017 is not a leap year, so 366 is invalid.)

Key Notes

1. If the day value exceeds the total number of days in the year (e.g., 366 in a non-leap year), the function returns NULL.
2. The function is useful for generating dates programmatically, such as for reports or date calculations.