Articles Cookbook
Back to list
SQL Server

How to Add Days to a Date in T-SQL

Database:

Operators:

DATEADD()

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.

CodeDepartureDate
LT20302019-02-20
GH11002019-03-01
SR54672019-12-30

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:

CodeChangedDepartureDate
LT20302019-02-22
GH11002019-03-03
SR54672020-01-01

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.

Recommended courses:

Recommended articles:

See also:

go to top