SQL - MONTH() Function

The month of a specific date is represented by the integer number returned by the SQL MONTH() function. It returns 1 for January, 2 for February, and so on. The argument for the MONTH() method can either be a literal date value or an expression that resolves to a TIME, DATE, SMALLDATETIME, DATETIME, DATETIME2, or DATETIMEOFFSET value.

MONTH() function returns the same value as the DATEPART().The DATEPART() function is an in-built function in SQL that is used to retrieve the specific segment (as an integer) from the date value such as (year, month, or day) or time value such as(hour, minute, or second).

Syntax

Following is the syntax of the SQL MONTH() function −

MONTH(date)

Parameters

This function accepts one parameter as discussed below −

  • date − date is an expression that has a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value that can be resolved. The date argument may be a string literal, an expression, a column expression, or a user-defined variable.

Note − If the given date contains only a time part the month() function returns 1.

Example

In the following query, we are retrieving the month from a particular date using the following query −

SELECT MONTH('2023/02/17 10:48') AS MONTH;

Output

When we execute the above query, the output is obtained as follows −

+-------+
| MONTH |
+-------+
| 2     |
+-------+

Example

Here, we are finding the month when the date contains only time part by using the following query −

SELECT MONTH('10:48') AS Result;

Output

On executing the above query, the output is displayed as follows −

+--------+
| Result |
+--------+
| 1      |
+--------+

Example

The MONTH() function will return a null value if the parameter passed was NULL.

In the following example we are going to pass NULL value as parameter to the month() function and retrieving the month by executing the below query.

SELECT MONTH(NULL) AS MONTH;

Output

The output for the above query is produced as given below − 

+-------+
| MONTH |
+-------+
| NULL  |
+-------+

Example

Look at the following example, where we are using the general way of getting the current month by using the following query −

SELECT MONTH(GETDATE()) AS 'Current Month';

Output

If we execute the above query, the result is produced as follows −

+--------------+
| CurrentMonth |
+--------------+
| 2            |
+--------------+

Example

Considering the following example, where we are using the CURRENT_TIMESTAMP for getting the current month by running the following query.

SELECT MONTH(CURRENT_TIMESTAMP) AS CurrentMonth;

Output

On executing the above query, it will generate the following output as shown below −

+--------------+
| CurrentMonth |
+--------------+
| 2            |
+--------------+

Example

Let us look into the following example, where we are using the date as a parameter and MONTH() function with a variable including time as well and running the query to retrieve the month −

DECLARE @DATE VARCHAR(22);
SET @DATE = '2023/02/17 11:11';
SELECT MONTH(@DATE) AS Month;

Output

On executing the above query, it will generate the following output as shown below −

+-------+
| Month |
+-------+
| 2     |
+-------+

Example

Assume we have created a table with the name IPLPlayers in the SQL database using the CREATE statement as shown in the query below −

CREATE TABLE IPLPlayers(
   ID INT,
   Name VARCHAR(255),
   Date_Of_Birth date,
   Country VARCHAR(255),
   PRIMARY KEY (ID)
);

Now, let us insert some records in the IPLPlayers table using INSERT statements as shown in the query below −

INSERT INTO IPLPlayers VALUES (1, 'Shikhar','1999-11-10','India');
INSERT INTO IPLPlayers VALUES (2, 'Dhoni','1992-12-11','Jharkhand');
INSERT INTO IPLPlayers VALUES (3, 'Gayle','1993-10-12','Westindies');
INSERT INTO IPLPlayers VALUES (4, 'Williamson','1992-09-23','NewZealand');

We can verify whether the table IPLPlayers is created or not using the following query −

SELECT * FROM IPLPlayers;

The table IPLPlayers is successfully created in the SQL database −

+----+------------+---------------+------------+
| ID | Name       | Date_Of_Birth | Country    |
+----+------------+---------------+------------+
| 1  | Shikhar    | 1999-11-10    | India      |
| 2  | Dhoni      | 1992-12-11    | Jharkhand  |
| 3  | Gayle      | 1993-10-12    | Westindies |
| 4  | Williamson | 1992-09-23    | NewZealand |
+----+------------+---------------+------------+

Now we are going to retrieve the month from all the entities in the column Date_Of_Birth from the table IPLPlayers by running the following query −

SELECT Name, Date_Of_Birth, Country,
MONTH(Date_Of_Birth) AS Month FROM IPLPlayers;

Output

When we execute the above query, the output is obtained as follows −

+------------+---------------+------------+-------+
| Name       | Date_Of_Birth | Country    | Month |
+------------+---------------+------------+-------+
| Shikhar    | 1999-11-10    | India      | 11    |
| Dhoni      | 1992-12-11    | Jharkhand  | 12    |
| Gayle      | 1993-10-12    | Westindies | 10    |
| Williamson | 1992-09-23    | NewZealand | 9     |
+------------+---------------+------------+-------+

Example

In the following example, we are using the variable with a MONTH() function and retrieving the month on the specified date by running the below query −

DECLARE @DATE VARCHAR(22);
SET @DATE = '2023/02/16';
SELECT MONTH(@DATE) AS Month;

Output

On running the above query, it will generate an output as shown below −

+-------+
| Month |
+-------+
| 2     |
+-------+
sql-date-functions.htm
Advertisements