MySQL - MAKEDATE() Function

The MySQL MAKEDATE() function is used to create a date value from the given day of an year.

This function accepts two numerical values representing year and day of the year as parameters (in the same order), creates a date value based on these values, and returns the result. If the value given for dayofyear is less than 0 this function returns NULL. And even if either of the arguments is NULL, the result is still NULL.

Syntax

Following is the syntax of MySQL MAKEDATE() function −

MAKEDATE(year,dayofyear)

Parameters

This method accepts two parameters. The same is described below −

  • year: The year for which you want to create a date.

  • dayofyear: The day of the year (1 to 366) for the specified year.

Return value

This function returns a date value representing the specified day of the year in the given year.

Example

In the following query, we are using the MySQL MAKEDATE() function to generates a date value from the given day of an year −

SELECT MAKEDATE(1947, 98) As Result;

Output

This will produce the following result −

Result
1947-04-08

Example

We can also pass arguments to this function as string values as shown below −

SELECT MAKEDATE('1890', '25') As Result;

Output

Following is the output −

Result
1890-01-25

Example

If the second argument (dayofyear) is 0, this function returns NULL −

SELECT MAKEDATE(0, 0) As Result;

Output

Following is the output −

Result
NULL

Example

If the argument passed for year is 0 this function assumes the given year as 2000 −

SELECT MAKEDATE(0, 1) As Result;

Output

Following is the output −

Result
2000-01-01

Example

If either of the arguments of this function is NULL it returns NULL −

SELECT MAKEDATE(NULL, 225) As Result;

Output

Following is the output −

Result
NULL

Example

Let us create a table with name PLAYERS in MySQL database using CREATE statement as shown below −

CREATE TABLE PLAYERS(
   ID int,
   NAME varchar(255),
   YEAROFBIRTH int,
   DAYOFYEAR int,
   COUNTRY VARCHAR(255),
   PRIMARY KEY (ID)
);

Now, we will insert 7 records in MyPlayers table using INSERT statements −

INSERT INTO PLAYERS VALUES
(1, 'Shikhar Dhawan', 1981, 339, 'India'),
(2, 'Jonathan Trott', 1981, 112, 'SouthAfrica'),
(3, 'Kumara Sangakkara', 1977, 300, 'Srilanka'),
(4, 'Virat Kohli', 1988, 310, 'India'),
(5, 'Rohit Sharma', 1987, 120, 'India'),
(6, 'Ravindra Jadeja', 1988, 341, 'India'),
(7, 'James Anderson', 1982, 181, 'England');

Execute the below query to fetch all the inserted records in the above-created table −

Select * From PLAYERS;

Following is the PLAYERS table −

ID NAME YEAROFBIRTH DAYOFYEAR COUNTRY
1 Shikhar Dhawan 1981 339 India
2 Jonathan Trott 1981 112 SouthAfrica
3 Kumara Sangakkara 1977 300 Srilanka
4 Virat Kohli 1988 310 India
5 Rohit Sharma 1987 120 India
6 Ravindra Jadeja 1988 341 India
7 James Anderson 1982 181 England

Now, we are using the MySQL MAKEDATE() function to retrieve and print the date of birth from the year and day of year values provided −

SELECT ID, NAME, COUNTRY, MAKEDATE(YEAROFBIRTH, DAYOFYEAR) 
As DateOfBirth From PLAYERS;

Output

The output is displayed as follows −

ID NAME COUNTRY DateOfBirth
1 Shikhar Dhawan India 1981-12-05
2 Jonathan Trott SouthAfrica 1981-04-22
3 Kumara Sangakkara Srilanka 1977-10-27
4 Virat Kohli India 1988-11-05
5 Rohit Sharma India 1987-04-30
6 Ravindra Jadeja India 1988-12-06
7 James Anderson England 1982-06-30
Advertisements