SQL Server MONTH()

SQL Server MONTH() Function

The SQL Server MONTH() function returns an integer representing the month part of a date. This function is useful for extracting the month from a date value.

Syntax

SELECT MONTH(date);

The MONTH() function takes a single argument:

  • date: The date from which to extract the month part.

Example SQL Server MONTH() Function Queries

Let's look at some examples of SQL Server MONTH() function queries:

1. Basic MONTH() Example

SELECT MONTH('2024-06-01') AS month_part;

This query returns the month part of the date '2024-06-01'. The result will be:

month_part
----------
6

2. Extracting the Month from a DateTime

SELECT MONTH('2024-06-01 12:34:56') AS month_part;

This query returns the month part of the datetime '2024-06-01 12:34:56'. The result will be:

month_part
----------
6

3. MONTH() with a Column

SELECT order_date, MONTH(order_date) AS month_part
FROM orders;

This query returns the month part of the order_date column for each record in the orders table. The result will show the original order_date and its corresponding month_part.

4. MONTH() with a Variable

DECLARE @date DATETIME;
SET @date = '2024-06-01';
SELECT MONTH(@date) AS month_part;

This query uses a variable to store a date and then returns the month part. The result will be:

month_part
----------
6

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the MONTH() function.

Step 1: Creating a Table

This step involves creating a new table named events to store some sample data with event dates.

CREATE TABLE events (
    id INT PRIMARY KEY,
    event_name VARCHAR(255),
    event_date DATETIME
);

In this example, we create a table named events with columns for id, event_name, and event_date.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the events table.

INSERT INTO events (id, event_name, event_date) VALUES (1, 'Event 1', '2024-06-01');
INSERT INTO events (id, event_name, event_date) VALUES (2, 'Event 2', '2024-12-25');
INSERT INTO events (id, event_name, event_date) VALUES (3, 'Event 3', '2024-08-15');

Here, we insert data into the events table.

Step 3: Using the MONTH() Function

This step involves using the MONTH() function to return the month part of the event_date column for each record in the events table.

SELECT id, event_name, event_date, MONTH(event_date) AS month_part
FROM events;

This query retrieves the id, event_name, event_date, and the month part of the event_date column for each row in the events table. The result will be:

id  event_name  event_date  month_part
--- ----------- ----------  ----------
1   Event 1     2024-06-01  6
2   Event 2     2024-12-25  12
3   Event 3     2024-08-15  8

Conclusion

The SQL Server MONTH() function is a powerful tool for extracting the month part of a date as an integer. Understanding how to use the MONTH() function and its syntax is essential for effective date manipulation and data processing in SQL Server.