SSIS DATEDIFF Function

The SSIS DATEDIFF function is a Date/Time function that will find the total number of date or time boundaries crossed between the given two date fields. This article explains how to use the DATEDIFF function with an example and the syntax for finding the difference between two given date values is as shown below.

DATEDIFF(<<datepart>>, <<startdate>>, <<enddate>>)

datepart: The DATEDIFF function consider the datepart as the date or time interval to identify the difference between the start and end date.

For instance, DATEDIFF(“m”, 2006-05-28, “2006-12-28) will add find the total number of months between the two dates and the result is 7. To demonstrate the SSIS DATEDIFF function, we use the Employee table below, which has 15 records.

Source Table

The below list shows the date part in the DATEDIFF function 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 DATEDIFF function

For this DATEDIFF 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 DATEDIFF 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 DATEDIFF function to the row cell under the Expression and replace the <<datepart>> with the year (yyyy), <<startdate>> as the HireDate column, and <<enddate>> as the current date (GETDATE()). Either you can manually delete the complete <<startdate>> and <<enddate>> expressions or drag and drop the HireDate column will automatically replace it.

The first expression returns the difference in the total number of years between the HireDate and the current date. The second SSIS DATEDIFF function expression finds the total number of hours difference between the local system time and the UTC time.

DATEDIFF( "yyyy", [HireDate], GETDATE() )

DATEDIFF( "hh", GETDATE(), GETUTCDATE() )
SSIS DATEDIFF Function Expression to find difference between two date or 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 DATEDIFF function package to see the years and hours difference between the two dates.

SSIS DATEDIFF Function to find difference between two date or time intervals