SQL DATEPART() Function

SQL DATEPART()

The SQL DATEPART function is used to extract a specific part of a date, such as the year, month, day, hour, minute, or second. It is particularly useful when filtering or organizing data based on date-related values.

In this tutorial, we will explore the DATEPART function, its syntax, and practical usage through examples.

---

Syntax of SQL DATEPART

The basic syntax of the DATEPART function is:

SELECT DATEPART(datepart, date_value) AS result;

Where:

• datepart: Specifies the part of the date to extract (e.g., year, month, day, hour, etc.).
• date_value: The date from which the part will be extracted.

---

Supported Date Parts in SQL DATEPART

Date Part	Description	Example
year / yy / yyyy	Extracts the year	DATEPART(year, '2024-02-12') → 2024
month / mm / m	Extracts the month	DATEPART(month, '2024-02-12') → 2
day / dd / d	Extracts the day	DATEPART(day, '2024-02-12') → 12
hour / hh	Extracts the hour	DATEPART(hour, '2024-02-12 14:30:00') → 14
minute / mi / n	Extracts the minute	DATEPART(minute, '2024-02-12 14:30:00') → 30
second / ss / s	Extracts the second	DATEPART(second, '2024-02-12 14:30:45') → 45

---

Step-by-Step Examples Using SQL DATEPART

1 Extracting Year and Month from a Date

Let’s create an employees table to demonstrate how to use the DATEPART function:

CREATE TABLE employees (
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(50),
    date_of_joining DATE
);

Insert sample data:

INSERT INTO employees (name, date_of_joining)
VALUES 
('Arjun', '2020-06-15'),
('Ram', '2022-11-10'),
('Priya', '2019-03-25');

Now, let’s extract the year and month when employees joined:

SELECT name, date_of_joining,
       DATEPART(year, date_of_joining) AS joining_year,
       DATEPART(month, date_of_joining) AS joining_month
FROM employees;