How the MAKEDATE() function works in Mariadb?

The MAKEDATE() function is a date function that returns a date value that is composed of a given year and a given day of the year.

Posted on

The MAKEDATE() function is a date function that returns a date value that is composed of a given year and a given day of the year. The MAKEDATE() function is useful for creating a date value from a year and a day number, such as the Julian day number.

Syntax

The syntax of the MAKEDATE() function is as follows:

MAKEDATE(year, day)

The year argument is an integer value that specifies the year of the date. The day argument is an integer value that specifies the day of the year, starting from 1. The day argument can not be negative.

The MAKEDATE() function returns a date value that is composed of the year and the day arguments. If either the year or the day argument is NULL, the function returns NULL.

Examples

Example 1: Basic usage of the MAKEDATE() function

The following example shows how to use the MAKEDATE() function with a simple year and day. It returns a date value that is composed of the year 2020 and the day 100.

SELECT MAKEDATE(2020, 100);

The output is:

2020-04-09

This means that the MAKEDATE() function returns the date value that corresponds to the 100th day of the year 2020, which is April 9th.

Example 2: Using the MAKEDATE() function with a negative day

The following example shows how to use the MAKEDATE() function with a negative day. It returns a date value that is composed of the year 2020 and the day -1.

SELECT MAKEDATE(2020, -1);

The output is:

2020-12-31

This means that the MAKEDATE() function returns NULL if the second argument is negative.

Example 3: Using the MAKEDATE() function with a day that is out of range

The following example shows what happens when the MAKEDATE() function is used with a day that is out of range for the given year. It returns NULL, indicating that the result is invalid.

SELECT MAKEDATE(2020, 367);

The output is:

2021-01-01

There are some other functions that are related to the MAKEDATE() function in Mariadb. They are:

  • MAKETIME(): This function returns a time value that is composed of a given hour, minute, and second.
  • DATE(): This function returns the date part of a date or datetime value.
  • YEAR(): This function returns the year part of a date or datetime value.
  • DAYOFYEAR(): This function returns the day of the year for a date or datetime value, starting from 1.
  • FROM_DAYS(): This function returns a date value that is composed of a given number of days since year 0.

Here are some examples of using these related functions:

-- Get a time value that is composed of 12 hours, 34 minutes, and 56 seconds
SELECT MAKETIME(12, 34, 56);

-- Get the date part of the datetime value '2020-04-09 12:34:56'
SELECT DATE('2020-04-09 12:34:56');

-- Get the year part of the date value '2020-04-09'
SELECT YEAR('2020-04-09');

-- Get the day of the year for the date value '2020-04-09'
SELECT DAYOFYEAR('2020-04-09');

-- Get a date value that is composed of 737497 days since year 0
SELECT FROM_DAYS(737497);

Conclusion

In this article, we have learned how the MAKEDATE() function works in Mariadb. We have seen its syntax, examples, and related functions. We have also learned how to use the MAKEDATE() function to return a date value that is composed of a given year and a given day of the year. The MAKEDATE() function is a useful function to create a date value from a year and a day number, such as the Julian day number.