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;

Explanation:

• DATEPART(year, date_of_joining) extracts the year when the employee joined.
• DATEPART(month, date_of_joining) extracts the month.
• This helps analyze employee hiring trends.

---

2. Extracting Day and Hour from a Timestamp

Suppose we have a orders table where we store order timestamps:

CREATE TABLE orders (
    id INT PRIMARY KEY IDENTITY(1,1),
    customer_name VARCHAR(50),
    order_timestamp DATETIME
);

Insert sample data:

INSERT INTO orders (customer_name, order_timestamp)
VALUES 
('Arjun', '2024-02-10 08:45:00'),
('Ram', '2024-02-11 14:30:00'),
('Priya', '2024-02-12 20:15:00');

Now, let’s extract the day and hour when orders were placed:

SELECT customer_name, order_timestamp,
       DATEPART(day, order_timestamp) AS order_day,
       DATEPART(hour, order_timestamp) AS order_hour
FROM orders;

Explanation:

• DATEPART(day, order_timestamp) extracts the day when the order was placed.
• DATEPART(hour, order_timestamp) extracts the hour of the order.
• This helps analyze order trends during different hours.

---

Conclusion

The SQL DATEPART() function is used to extract specific date parts, making it useful for data analysis and reporting. In this tutorial, we covered:

1. Different date parts supported by DATEPART.
2. How to use it to extract the year, month, day, and hour.
3. Examples using employee joining dates and order timestamps.