SSIS YEAR Function

The SSIS YEAR function is a Date/Time function that returns the Year number (0001 to 9999) from the given date in integer format. This article explains how to use the YEAR function with an example and the syntax for getting the Year number from the date value is shown below.


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

Source Table

SSIS YEAR function

For this YEAR example, 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 YEAR 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 YEAR function to the row cell under the Expression and replace the <<date>> with the DateTime column to return YEAR numbers (0001 to 9999). Either you can manually delete the complete <<date>> expression or drag and drop the HireDate column will automatically replace it.

The first expression returns the YEAR number from the HireDate columns. The second SSIS YEAR function expression returns the YEAR number from the current system date.

YEAR( [HireDate] )

SSIS YEAR 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 YEAR function package to see the Year number (0001 to 9999) from the HireDate and the current system date.

SSIS YEAR Function output