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 change a date and/or time by adding a specific number of a chosen unit of time, use SQL Server’s 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 column containing the date/time/datetime 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 ‘2019-02-20’ is now ‘2019-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.