SSIS DATEPART Function

The SSIS DATEPART function is a Date/Time function that will return the specific part of the date from the given date field. This article explains how to use the DATEPART function with an example and the syntax for returning a specific date part is as shown below.

DATEPART(<<datepart>>, <<date>>)

datepart: The SSIS DATEPART function returns the integer value belonging this argument. For instance, DATEPART(“m”, 2006-05-28) will return month number and the result is 5. The below list shows the date part and the descriptions.

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

To demonstrate the SSIS DATEPART function, we use the Employee table below, which has 15 records. Please refer to the Union All Transformation, Derived Column Transformation, Built-in Functions, and SQL Server articles in SSIS.

Source Table

SSIS DATEPART function

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

From the below expressions, yyyy returns the year part, mm returns month umber, dd returns day number, and hh returns the hour.

DATEPART( "yyyy", [HireDate] )

DATEPART( "mm", [HireDate] )

DATEPART( "dd", [HireDate] )

DATEPART( "hh", [HireDate] )
SSIS DATEPART Function Expression

Click OK to close the Editor window.

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 DATEPART function package to see the year, month, day, and hour values from the HireDate column.

SSIS DATEPART Function output