MySQL - MAKEDATE() Function

---

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

The MYSQL MAKEDATE() 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.

Syntax

Following is the syntax of the above function –

MAKEDATE(year,dayofyear)

Example 1

Following example demonstrates the usage of the MAKEDATE() function –

mysql> SELECT MAKEDATE(1947, 98);
+--------------------+
| MAKEDATE(1947, 98) |
+--------------------+
| 1947-04-08         |
+--------------------+
1 row in set (1.17 sec)
mysql> SELECT MAKEDATE(2010, 365);
+---------------------+
| MAKEDATE(2010, 365) |
+---------------------+
| 2010-12-31          |
+---------------------+
1 row in set (0.00 sec)

Example 2

Following is another example of this function –

mysql> SELECT MAKEDATE(2009, 5464);
+----------------------+
| MAKEDATE(2009, 5464) |
+----------------------+
| 2023-12-17           |
+----------------------+
1 row in set (0.00 sec)
mysql> SELECT MAKEDATE(2010, 1097);
+----------------------+
| MAKEDATE(2010, 1097) |
+----------------------+
| 2013-01-01           |
+----------------------+
1 row in set (0.00 sec)

Example 3

You can also pass arguments to this function as s string values.

mysql> SELECT MAKEDATE('1890', '25');
+------------------------+
| MAKEDATE('1890', '25') |
+------------------------+
| 1890-01-25             |
+------------------------+
1 row in set (0.00 sec)

Example 4

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

mysql> SELECT MAKEDATE(0, 0);
+----------------+
| MAKEDATE(0, 0) |
+----------------+
| NULL           |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT MAKEDATE('1989', '0');
+-----------------------+
| MAKEDATE('1989', '0') |
+-----------------------+
| NULL                  |
+-----------------------+
1 row in set (0.00 sec)

Example 5

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

mysql> SELECT MAKEDATE(0, 1);
+----------------+
| MAKEDATE(0, 1) |
+----------------+
| 2000-01-01     |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT MAKEDATE(0, 367);
+------------------+
| MAKEDATE(0, 367) |
+------------------+
| 2001-01-01       |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT MAKEDATE(0, 732);
+------------------+
| MAKEDATE(0, 732) |
+------------------+
| 2002-01-01       |
+------------------+
1 row in set (0.00 sec)

Example 6

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

mysql> SELECT MAKEDATE(NULL, 225);
+---------------------+
| MAKEDATE(NULL, 225) |
+---------------------+
| NULL                |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT MAKEDATE(1998, NULL);
+----------------------+
| MAKEDATE(1998, NULL) |
+----------------------+
| NULL                 |
+----------------------+
1 row in set (0.00 sec)

Example 7

You can also pass the column name as an argument to this function. Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below –

mysql> CREATE TABLE ExhibitonMatchPlayers(
	ID INT,
	First_Name VARCHAR(255),
	Last_Name VARCHAR(255),
	Year_Of_Birth INT,
	DayOfYear_Of_Birth INT,
	Place_Of_Birth VARCHAR(255),
	Country VARCHAR(255),
	PRIMARY KEY (ID)
);

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

insert into ExhibitonMatchPlayers values(1, 'Shikhar', 'Dhawan', 1981, 339, 'Delhi', 'India');
insert into ExhibitonMatchPlayers values(2, 'Jonathan', 'Trott', 1981, 112, 'CapeTown', 'SouthAfrica');
insert into ExhibitonMatchPlayers values(3, 'Kumara', 'Sangakkara', 1977, 300, 'Matale', 'Srilanka');
insert into ExhibitonMatchPlayers values(4, 'Virat', 'Kohli', 1988, 310, 'Delhi', 'India');
insert into ExhibitonMatchPlayers values(5, 'Rohit', 'Sharma', 1987, 120, 'Nagpur', 'India');
insert into ExhibitonMatchPlayers values(6, 'Ravindra', 'Jadeja', 1988, 341, 'Nagpur', 'India');
insert into ExhibitonMatchPlayers values(7, 'James', 'Anderson', 1982, 181, 'Burnley', 'England');

Following query retrieves and prints the date of birth from the year and day of year values provided —

mysql> select First_Name, Last_Name, Year_Of_Birth, DayOfYear_Of_Birth, Place_Of_Birth, Country, MAKEDATE(Year_Of_Birth, DayOfYear_Of_Birth) as DateOfBirth from ExhibitonMatchPlayers;
+------------+------------+---------------+--------------------+----------------+-------------+-------------+
| First_Name | Last_Name  | Year_Of_Birth | DayOfYear_Of_Birth | Place_Of_Birth | Country     | DateOfBirth |
+------------+------------+---------------+--------------------+----------------+-------------+-------------+
| Shikhar    | Dhawan     | 1981          | 339                | Delhi          | India       | 1981-12-05  |
| Jonathan   | Trott      | 1981          | 112                | CapeTown       | SouthAfrica | 1981-04-22  |
| Kumara     | Sangakkara | 1977          | 300                | Matale         | Srilanka    | 1977-10-27  |
| Virat      | Kohli      | 1988          | 310                | Delhi          | India       | 1988-11-05  |
| Rohit      | Sharma     | 1987          | 120                | Nagpur         | India       | 1987-04-30  |
| Ravindra   | Jadeja     | 1988          | 341                | Nagpur         | India       | 1988-12-06  |
| James      | Anderson   | 1982          | 181                | Burnley        | England     | 1982-06-30  |
+------------+------------+---------------+--------------------+----------------+-------------+-------------+
7 rows in set (0.00 sec)

Example 8

Suppose we have created a table named SubscriberInfo with 5 records in it using the following queries –

mysql> CREATE TABLE SubscriberInfo (
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	Year INT,
	DayOfYear INT
);
insert into SubscriberInfo values('Raja', 'Premium', 2020, 225);
insert into SubscriberInfo values('Roja', 'Basic', 2020, 299);
insert into SubscriberInfo values('Puja', 'Moderate', 2021, 300);
insert into SubscriberInfo values('Vanaja', 'Basic', 2021, 110);
insert into SubscriberInfo values('Jalaja', 'Premium', 2021, 315);

In the following query we are passing column names Year and DayOfYear as arguments to the MAKEDATE() function and trying to retrieve the subscription date.

mysql> SELECT SubscriberName, PackageName, Year, DayOfYear, MAKEDATE(Year, DayOfYear) as SusbscriptionDate FROM SubscriberInfo;
+----------------+-------------+------+-----------+-------------------+
| SubscriberName | PackageName | Year | DayOfYear | SusbscriptionDate |
+----------------+-------------+------+-----------+-------------------+
| Raja           | Premium     | 2020 | 225       | 2020-08-12        |
| Roja           | Basic       | 2020 | 299       | 2020-10-25        |
| Puja           | Moderate    | 2021 | 300       | 2021-10-27        |
| Vanaja         | Basic       | 2021 | 110       | 2021-04-20        |
| Jalaja         | Premium     | 2021 | 315       | 2021-11-11        |
+----------------+-------------+------+-----------+-------------------+
5 rows in set (0.00 sec)