How to Add Days to a Date in T-SQL

Problem

You’d like to add a given number of days to a date in T-SQL.

Example

Our database has a table named Flight with data in the columns Code and DepartureDate.

Let’s change the departure date for all flights, adding two days to the current departure date.

Solution

We will use the DATEADD() function to specify the unit of time to add, define how much to add, and select the date to change. Have a look at the query:

SELECT 
  Code,
  DATEADD(day, 2, DepartureDate) AS ChangedDepartureDate 
FROM Flight;

Here’s the result:

Discussion

To add a specific number of a days to a date or time value in SQL Server, use DATEADD() function. This function works on date, time, or date and time data types. It takes three arguments:

1. The desired unit of date/time to add. In our example, it is day; we want to add days to the date.
2. How many units to add. In our example, this is 2; we want to add 2 days to the existing date.
3. A date/time/datetime value we want to change. In our example, we use the DepartureDate column. This argument can also be an expression that returns a date/time/datetime.

The DATEADD() function returns a new date. In our example, the new date is returned as the ChangedDepartureDate column. For the LT2030 flight code, the date 2023-02-20 is now 2023-02-22.

The function DATEADD() can use date and time units like year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, etc. You can learn more in the SQL Server documentation.