How the LAST_DAY() function works in Mariadb?

The LAST_DAY() function is a date and time function that returns the last day of the month for a given date. It can be used to calculate the end of a billing cycle, the expiration date of a subscription, or the number of days in a month. In this article, we will learn how the LAST_DAY() function works in Mariadb, what are its syntax and parameters, and how to use it with some examples. We will also explore some related functions that can be used with the LAST_DAY() function.

Syntax

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

LAST_DAY(date)

The function takes one argument:

date: The date for which to return the last day of the month. It can be any valid date or datetime expression, or a string that can be converted to a date or datetime.

The function returns a date value that represents the last day of the month for the given date. If the argument is NULL, the function returns NULL. If the argument is invalid, the function returns an error.

Examples

Let’s see some examples of how to use the LAST_DAY() function in Mariadb.

Example 1: Using the current date

In this example, we will use the LAST_DAY() function with the current date. We will use the CURDATE() function to get the current date. For example, if the current date is 2024-02-12, the function will return the last day of February 2024.

SELECT LAST_DAY(CURDATE());

2024-02-29

The function returns a date value that represents the last day of the current month. Note that in this case, the month is February and the year is a leap year, so the last day is the 29th.

Example 2: Using a specific date

In this example, we will use the LAST_DAY() function with a specific date. We will use the date 2024-04-15, which is the 15th of April 2024. The function will return the last day of April 2024.

SELECT LAST_DAY('2024-04-15');

2024-04-30

The function returns a date value that represents the last day of the given month. Note that in this case, the month is April and the year is not a leap year, so the last day is the 30th.

Example 3: Using a datetime expression

In this example, we will use the LAST_DAY() function with a datetime expression. We will use the expression NOW() + INTERVAL 3 MONTH, which adds three months to the current datetime. For example, if the current datetime is 2024-02-12 21:38:20, the expression will return 2024-05-12 21:38:20. The function will return the last day of May 2024.

SELECT LAST_DAY(NOW() + INTERVAL 3 MONTH);

2024-05-31

The function returns a date value that represents the last day of the month for the given datetime expression. Note that in this case, the month is May and the year is not a leap year, so the last day is the 31st.

There are some related functions that can be used with the LAST_DAY() function in Mariadb. Here are some of them:

DAY(): This function returns the day of the month for a given date. It can be used to get the number of days in a month by using the LAST_DAY() function. For example:
```
SELECT DAY(LAST_DAY('2024-02-12'));
```
```
29
```

The function returns the day of the month for the last day of the given date. Note that in this case, the month is February and the year is a leap year, so the last day is the 29th.

DATE_FORMAT(): This function formats a date or datetime value according to a specified format. It can be used to display the last day of the month in a different format by using the LAST_DAY() function. For example:
```
SELECT DATE_FORMAT(LAST_DAY('2024-04-15'), '%W, %M %e, %Y');
```
```
Tuesday, April 30, 2024
```
The function returns a formatted string that represents the last day of the month for the given date. Note that in this case, the format is '%W, %M %e, %Y', which displays the day of the week, the month name, the day of the month, and the year.
DATE_SUB(): This function subtracts a specified time interval from a date or datetime value. It can be used to get the previous month’s last day by using the LAST_DAY() function. For example:
```
SELECT DATE_SUB(LAST_DAY('2024-04-15'), INTERVAL 1 MONTH);
```
```
2024-03-31
```

The function returns a date value that represents the previous month’s last day for the given date. Note that in this case, the previous month is March and the year is not a leap year, so the last day is the 31st.

Conclusion

In this article, we learned how the LAST_DAY() function works in Mariadb, what are its syntax and parameters, and how to use it with some examples. We also explored some related functions that can be used with the LAST_DAY() function. The LAST_DAY() function is a useful tool for getting the last day of the month for any date or datetime value.