How the MONTH() function works in Mariadb?

The MONTH() function is a date and time function that returns the month number for a given date.

Posted on

The MONTH() function is a date and time function that returns the month number for a given date. The month number is returned as an integer value ranging from 1 to 12, where 1 represents January and 12 represents December. The function can accept various types of arguments, such as date, datetime, or string values.

Syntax

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

MONTH(date)

The date argument is a valid date or datetime expression that represents the date for which the month number is returned. The date argument can also be a string in a format that can be converted to a date or datetime value.

Examples

Example 1: Basic usage

The following example shows how to use the MONTH() function to return the month number for a given date:

SELECT MONTH('2024-02-13');

The output is:

2

The function returns 2, as it is the month number for February.

Example 2: Using a datetime expression

The following example shows how to use the MONTH() function with a datetime expression as the date argument:

SELECT MONTH(NOW());

The output is:

2

The function returns the month number of the current date, as it evaluates the NOW() function to get the current datetime value.

Example 3: Using a string as the date argument

The following example shows how to use the MONTH() function with a string as the date argument:

SELECT MONTH('13 Feb 2024');

The output is:

2

The function returns the month number, as it converts the string to a date value.

Example 4: Handling invalid arguments

The following example shows how the MONTH() function handles invalid arguments:

SELECT MONTH('2024-02-31');

The output is:

NULL

The function returns NULL, as it cannot parse the string as a valid date.

Example 5: Using the MONTH() function in a WHERE clause

The following example shows how to use the MONTH() function in a WHERE clause to filter the records based on the month number:

SELECT * FROM orders
WHERE MONTH(order_date) = 2;

The output is:

order_id | customer_id | order_date | amount
---------|-------------|------------|-------
1001     | 101         | 2024-02-05 | 500.00
1003     | 102         | 2024-02-10 | 300.00
1005     | 103         | 2024-02-15 | 400.00

The query returns the orders that have the order date in February.

Some of the functions that are related to the MONTH() function are:

  • MONTHNAME() function: Returns the name of the month for a given date. The name of the month is returned as a string in the current language setting of the server. The function can also accept an optional second argument that specifies the locale for the month name.
  • DAY() function: Returns the day of the month for a given date, ranging from 1 to 31.
  • YEAR() function: Returns the year for a given date, as a four-digit number.
  • EXTRACT() function: Returns a single part of a date or datetime value, such as year, month, day, hour, minute, or second.

For example, the following query uses the MONTHNAME(), DAY(), and YEAR() functions to return the formatted date for a given date:

SELECT CONCAT(MONTHNAME('2024-02-13'), ' ', DAY('2024-02-13'), ', ', YEAR('2024-02-13')) AS formatted_date;

The output is:

February 13, 2024

Conclusion

The MONTH() function is a useful function to get the month number for a given date. The function can handle various types of arguments, such as date, datetime, or string values. The function returns an integer value ranging from 1 to 12, where 1 represents January and 12 represents December. The function is related to other date and time functions, such as MONTHNAME(), DAY(), YEAR(), and EXTRACT() functions.