SQL Server YEAR()

SQL Server YEAR() Function

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

Syntax

SELECT YEAR(date);

The YEAR() function takes a single argument:

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

Example SQL Server YEAR() Function Queries

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

1. Basic YEAR() Example

SELECT YEAR('2024-06-01') AS year_part;

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

year_part
---------
2024

2. Extracting the Year from a DateTime

SELECT YEAR('2024-06-01 12:34:56') AS year_part;

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

year_part
---------
2024

3. YEAR() with a Column

SELECT order_date, YEAR(order_date) AS year_part
FROM orders;

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

4. YEAR() with a Variable

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

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

year_part
---------
2024

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the YEAR() 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 YEAR() Function

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

SELECT id, event_name, event_date, YEAR(event_date) AS year_part
FROM events;

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

id  event_name  event_date  year_part
--- ----------- ----------  ---------
1   Event 1     2024-06-01  2024
2   Event 2     2024-12-25  2024
3   Event 3     2024-08-15  2024

Conclusion

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