SSIS DATEADD Function

The SSIS DATEADD function is a Date/Time function that will add or subtract the date or time interval from the given date and return a new DT_DBTIMESTAMP. This article explains how to use the DATEADD function with an example and the syntax for predicting future dates or analyzing past dates from the date value is as shown below.

DATEADD(<<datepart>>, <<number>>, <<date>>)
  • datepart: The DATEADD function consider the datepart as the date or time interval to add or subtract from the date. For instance, yyyy means add years.
  • number: It accepts the integer value and you have to mention how many date or time intervals you want to add or subtract.
  • Date: Actual Date

For instance, DATEADD(“m”, 6, “2006-01-28) will add six months to the given date and the result is 2006-07-28. To demonstrate the SSIS DATEADD function, we use the Employee table below, which has 15 records.

Source Table

The below list shows the date part and the descriptions.

  1. Year =”yyyy”
  2. Month = “m”
  3. Day = “d”
  4. Quarter = “q”
  5. Week = “ww”
  6. Weekday =”w”
  7. Day of Week=”y”
  8. Hour = “h”
  9. Minute = “n”
  10. Second = “s”

SSIS DATEADD function

For this DATEADD example, let me add a Data Flow Task and double-click on it to enter the Data Flow region. Next, add the OLE DB Source to read data from the above SQL database table. Please join the OLE DB Source to Derived Column Transformation because it helps to write DATEADD expressions and create new columns.

Data Flow to add Derived Column

Next, double-click on it to open the Derived Column Transformation Editor. Drag and drop the SSIS DATEADD function to the row cell under the Expression and replace the <<datepart>> with the year (yyyy), <number> with 2 and -5, and <<date>> with the HireDate column to add and subtract years. Either you can manually delete the complete <<date>> expression or drag and drop the HireDate column will automatically replace it.

The first SSIS DATEADD function expression returns adds two years to the HireDate and the second expression subtracts five years from the HireDate.

DATEADD( "yyyy", 2, [HireDate] )

DATEADD( "yyyy", -5, [HireDate] )
SSIS DATEADD Function Expression to add or subtract date and time intervals

Click OK to close the Editor window. Please refer to the Union All Transformation, Derived Column Transformation, Built-in Functions, and SQL Server articles in SSIS.

Drag and drop the Union All Transformation and connect the Derived Column to it. Next, right-click on the connection node and choose the Enable Data Viewer. Please run the SSIS DATEADD function package to see the past and future dates of the HireDate column.

SSIS DATEADD Function to add or subtract date and time intervals