SQL Reference - DAYOFMONTH

DAYOFMONTH(date)

date: The date or datetime from which to extract the day of the month. This parameter should be in ‘YYYY-MM-DD’ or ‘YYYY-MM-DD HH

Example

SELECT DAYOFMONTH('2022-07-18') AS 'Day of the Month';

Output

+------------------+
| Day of the Month |
+------------------+
|               18 |
+------------------+

Explanation

The DAYOFMONTH function in MySQL is used to return the day of the month for a specified date. The date is passed as an argument in the format ‘YYYY-MM-DD’. In the given example, ‘2022-07-18’ is the input date, and the function DAYOFMONTH returns ‘18’ as the day of the month.

DAY(date) AS DayOfMonth

date: This parameter refers to the date from which the “day of the month” is to be extracted. It accepts a date or datetime expression. Regardless if the given date is in yyyy-mm-dd, dd-mm-yyyy or any other format, DAY(date) will return the day component of that date as an integer. It ranges from 1 through 31 representing the day of the specified date.

Example

SELECT DAY(GETDATE()) AS 'Day of Month';

Output

'Day of Month'
--------------
       24

Explanation

The DAY function in SQL Server returns the day of the month from a given date. GETDATE() is a built-in SQL Server function that returns the current date and time. The result of this command will depend on the current date at which it is run. In this case, the day of the month is 24.

DAYOFMONTH(date)

date: This parameter represents the date input from which the day of the month will be extracted. It must be a valid date expression. The function will return an integer, ranging from 1 to 31, that corresponds to the day of the month of the input date. The date parameter can be a column of a table, a literal, or the result of an expression; as long as it evaluates to a date value.

Example

SELECT DAYOFMONTH(TO_DATE('2022-10-21','YYYY-MM-DD')) FROM dual;

Output

Explanation

The above SQL command retrieves the day of the month from a specified date. In this case, it extracts the day ‘21’ from the date ‘2022-10-21’.