SQL DATEADD()

The SQL DATEADD() function is used to add a specific time interval to a date value. This function is commonly used to manipulate dates by adding days, months, years, hours, or other time units.

In this tutorial, we will explore the DATEADD function, its syntax, and how to use it effectively with practical examples.

Syntax of SQL DATEADD Function

The syntax of the DATEADD function is as follows:

</>
Copy
DATEADD(interval, number, date_value)

Parameters:

  • interval: The unit of time to add (e.g., year, month, day, hour, minute).
  • number: The number of intervals to add to the date.
  • date_value: The starting date to which the interval is added.

Common Interval Values:

IntervalDescription
yearYear
monthMonth
dayDay
hourHour
minuteMinute
secondSecond

Step-by-Step Examples Using SQL DATEADD

1 Adding Days to a Date

Let’s assume we have a table named employees that stores employee details, including their joining date.

We create the table in Microsoft SQL Server:

</>
Copy
CREATE TABLE employees (
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(50),
    joining_date DATE
);

Insert some sample data:

</>
Copy
INSERT INTO employees (name, joining_date)
VALUES 
('Arjun', '2023-01-15'),
('Ram', '2022-11-20'),
('Priya', '2023-05-10');

Now, let’s use the DATEADD function to calculate the review date by adding 180 days to each employee’s joining date:

</>
Copy
SELECT name, joining_date, DATEADD(day, 180, joining_date) AS review_date
FROM employees;

Explanation:

  • DATEADD(day, 180, joining_date) adds 180 days to the employee’s joining date.
  • The new date is displayed as review_date.

2 Adding Months to a Date

Suppose we want to calculate the expiration date of a subscription by adding 6 months to the start date.

Create a subscriptions table in Microsoft SQL Server:

</>
Copy
CREATE TABLE subscriptions (
    id INT PRIMARY KEY IDENTITY(1,1),
    customer_name VARCHAR(50),
    start_date DATE
);

Insert sample data:

</>
Copy
INSERT INTO subscriptions (customer_name, start_date)
VALUES 
('Arjun', '2024-02-01'),
('Ram', '2024-01-10'),
('Priya', '2024-03-15');

Use DATEADD to calculate the expiration date:

</>
Copy
SELECT customer_name, start_date, DATEADD(month, 6, start_date) AS expiration_date
FROM subscriptions;

Explanation:

  • DATEADD(month, 6, start_date) adds 6 months to the subscription start date.
  • The result is displayed as expiration_date.

Conclusion

The SQL DATEADD() function is a powerful tool for date manipulation in SQL. In this tutorial, we covered:

  1. The syntax and usage of the DATEADD function.
  2. A list of commonly used intervals such as day, month, and year.
  3. Practical examples, including adding days and months to dates.