SQL Server DATEADD()

SQL Server DATEADD() Function

The SQL Server DATEADD() function adds a specified number to a specified part of a date. This function is useful for performing date and time calculations.

Syntax

SELECT DATEADD(datepart, number, date);

The DATEADD() function takes three arguments:

  • datepart: The part of the date to which the number is added. Common values include year, quarter, month, day, week, hour, minute, second, and millisecond.
  • number: The number of dateparts to add to the date. This can be a positive or negative integer.
  • date: The date to which the number is added.

Example SQL Server DATEADD() Function Queries

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

1. Adding Days to a Date

SELECT DATEADD(day, 10, '2024-06-01') AS new_date;

This query adds 10 days to the date '2024-06-01'. The result will be:

new_date
----------
2024-06-11

2. Subtracting Months from a Date

SELECT DATEADD(month, -2, '2024-06-01') AS new_date;

This query subtracts 2 months from the date '2024-06-01'. The result will be:

new_date
----------
2024-04-01

3. Adding Hours to a Date

SELECT DATEADD(hour, 5, '2024-06-01 12:00:00') AS new_date;

This query adds 5 hours to the datetime '2024-06-01 12:00:00'. The result will be:

new_date
-------------------
2024-06-01 17:00:00

4. DATEADD() with a Column

SELECT order_date, DATEADD(day, 7, order_date) AS delivery_date
FROM orders;

This query adds 7 days to the order_date column for each record in the orders table. The result will show the original order_date and the corresponding delivery_date.

5. DATEADD() with a Variable

DECLARE @order_date DATETIME;
SET @order_date = '2024-06-01 08:00:00';
SELECT DATEADD(hour, 3, @order_date) AS new_date;

This query uses a variable to store a datetime value and then adds 3 hours to it. The result will be:

new_date
-------------------
2024-06-01 11:00:00

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the DATEADD() 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 10:00:00');
INSERT INTO events (id, event_name, event_date) VALUES (2, 'Event 2', '2024-07-01 14:00:00');
INSERT INTO events (id, event_name, event_date) VALUES (3, 'Event 3', '2024-08-01 09:00:00');

Here, we insert data into the events table.

Step 3: Using the DATEADD() Function

This step involves using the DATEADD() function to add a specified number of days to the event_date column.

SELECT id, event_name, event_date, DATEADD(day, 7, event_date) AS new_date
FROM events;

This query retrieves the id, event_name, event_date, and the new date after adding 7 days to the event_date column for each row in the events table. The result will be:

id  event_name  event_date            new_date
--- ----------- -------------------  -------------------
1   Event 1     2024-06-01 10:00:00  2024-06-08 10:00:00
2   Event 2     2024-07-01 14:00:00  2024-07-08 14:00:00
3   Event 3     2024-08-01 09:00:00  2024-08-08 09:00:00

Conclusion

The SQL Server DATEADD() function is a powerful tool for adding a specified number to a specified part of a date. Understanding how to use the DATEADD() function and its syntax is essential for effective date and time calculations in SQL Server.