In this tutorial, we will study the MySQL MAKEDATE() function. Suppose someone tells you that their date of birth is the 45th day in the year 2000 and you need to enter that in their table. Of course, you could always ask them to be more specific but let’s just take this challenge, shall we? One way to find out their birthday is counting till the 45th day in 2000 and entering that in the table. The other way is to use the MAKEDATE() function.
MySQL provides us with the MAKEDATE() function which, you read that right, is used to make a date value. Let me be more specific. MAKEDATE() takes in two arguments, a year value and a number of days value and creates and returns a date value.
So if you pass the year 2000 and 45 as the number of days to the MAKEDATE() function, it will return the date value as 14th February 2000. Convenient, isn’t it? Let us dive into the function’s syntax and examples.
Syntax Of MySQL MAKEDATE()
MAKEDATE(year, number_of_days)Code language: SQL (Structured Query Language) (sql)Where, ‘year’ is a required parameter which is a 4 digit value that specifies the year value in the date you want to create and,
‘number_of_days’ is a required parameter that specifies the day of the year. It’s value should be greater than 0.
Examples of MySQL MAKEDATE()
Let us begin with some basic examples. Let us use the MAKEDATE() function to create the date values for the following – 15th day of 2021 and the 32nd day of 2021. We will use the SELECT statement and an alias called ‘Date’ to make our output readable. The queries are –
SELECT MAKEDATE(2021, 15) AS Date;
SELECT MAKEDATE(2021, 32) AS Date;Code language: SQL (Structured Query Language) (sql)And the output is –
Now these were very predictable values. Let us take a greater value. How about finding the date values for the 350th day of 2020 and the 286th day of 2020? The queries for it are –
SELECT MAKEDATE(2020, 350) AS Date;
SELECT MAKEDATE(2020, 286) AS Date;Code language: SQL (Structured Query Language) (sql)And the output is –
MySQL MAKEDATE() With A Very Large ‘Number Of Days’ Value
We know that every non-leap year has 365 days while leap years have 366 days. What if we pass a year, say 2020 and the number of days value as 500? But wait, 2020, a leap year has only 366 days! How does MySQL MAKEDATE() behave when we pass 500 as the ‘number of days’ parameter? Well, let us see the two queries below.
SELECT MAKEDATE(2020, 500) AS Date;
SELECT MAKEDATE(2020, 3000) AS Date;Code language: SQL (Structured Query Language) (sql)And the output is –
What happens is if we specify a value greater than 365 or 366, the result will flick over to the next calendar year as required and begin counting days in that. That is what happens in the above queries. The 366 days of 2020 are counted and then the remaining number of days are counted in 2021. Similarly, if we pass 3000 as the number of days (as in the second query), the calendar years keep getting flicked ahead till the count gets over. In this case, it gets over finally at the date 2028-03-18.
MySQL MAKEDATE() With Leap Years
Talking about leap years, MySQL MAKEDATE() is smart enough to differentiate between a leap year and a non-leap year to give us the exact date value. Let us see an example of this. Consider the below query.
SELECT MAKEDATE(2020, 60) AS 'Leap Year Date', MAKEDATE(2021, 60) AS 'Non Leap Year Date';Code language: SQL (Structured Query Language) (sql)And the output is –
MySQL MAKEDATE() With Zero Days
The ‘number_of_days’ parameter should be a value greater than 0. If we pass 0 as the ‘number_of_days’ parameter, we get the result as NULL. Let us see an example of this. Consider the below query.
SELECT MAKEDATE(2019, 0) AS Date;Code language: SQL (Structured Query Language) (sql)And the output is –
Working With Tables
Let us now see an example of MAKEDATE() with tables. Consider the below Persons table.
Let us create a new column called ‘DOB’ of the date datatype using the ALTER statement. Next, let us fill in the date of birth of the 3 people. Rahul Khanna’s day of birth is the 75th day of the year 1999. Robert Crawley’s day of birth is the 118th day of the year 1999 and Devika Kulkarni’s day of birth is the 258th day of the year 1999.
Let us use the MySQL MAKEDATE() function with the UPDATE statement to convert these ‘day of birth’ values to ‘date of birth’ values and put them in the DOB column for the corresponding person. The queries are –
ALTER TABLE Persons ADD DOB date;
UPDATE Persons SET DOB=MAKEDATE(1999, 75) WHERE PersonID=1;
UPDATE Persons SET DOB=MAKEDATE(1999, 118) WHERE PersonID=2;
UPDATE Persons SET DOB=MAKEDATE(1999, 258) WHERE PersonID=3;
SELECT * FROM Persons;Code language: SQL (Structured Query Language) (sql)And the output is –
Conclusion
The MAKEDATE() function is very useful. It helps you to make date values by passing the day number and the year values. I would encourage you to practice some more queries with this function.
References
- MySQL Official Documentation on the
MAKEDATE()function.