SQL DATEDIFF Function

SQL DATEDIFF Function

The SQL DATEDIFF function is used to calculate the difference between two dates in terms of days. It returns the number of days between the start date and the end date. This function is commonly used in reporting, tracking events, and calculating age.

In this tutorial, we will explore the DATEDIFF function, its syntax, and practical examples.

---

Syntax of SQL DATEDIFF Function

The syntax of the DATEDIFF function varies slightly depending on the database system, but the general format is:

SELECT DATEDIFF(end_date, start_date);

Parameters:

• start_date: The starting date (earlier date).
• end_date: The ending date (later date).
• Returns: The number of days between start_date and end_date.

The function counts the number of whole days between the two dates and does not consider time differences.

---

Step-by-Step Examples Using SQL DATEDIFF

1. Calculating the Difference Between Two Dates

Let’s say we need to calculate the number of days between ‘2024-02-01’ and ‘2024-02-12’. We can use the following query:

SELECT DATEDIFF('2024-02-12', '2024-02-01') AS days_difference;

Output:

Explanation:

• The DATEDIFF function subtracts ‘2024-02-01’ from ‘2024-02-12’.
• Since there are 11 days between the two dates, the result is 11.

---

2. Using DATEDIFF in a Table Query

Consider a employees table where we store employees’ names and their joining dates. We want to find the number of days each employee has been in the company.

Creating the Employees Table

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    joining_date DATE
);

Inserting sample data:

INSERT INTO employees (name, joining_date)
VALUES 
('Arjun', '2023-06-15'),
('Ram', '2022-11-10'),
('Priya', '2021-09-05');

Now, let’s use the DATEDIFF function to find how many days each employee has worked until today (‘2025-02-12’).

SELECT name, joining_date, 
DATEDIFF('2025-02-12', joining_date) AS days_in_company
FROM employees;

Output:

Explanation:

• The DATEDIFF function calculates the difference between today (‘2025-02-12’) and the employee’s joining date.
• The result shows how many days each employee has worked in the company.

---

Conclusion

The DATEDIFF function is an essential tool in SQL for date-based calculations. It helps determine the number of days between two dates, making it useful for:

• Tracking employee tenure.
• Calculating the number of days between events.
• Analyzing business performance over time.

Mastering DATEDIFF allows efficient handling of date-based queries in SQL.