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.