CODEWITHSUNDEEP
sql-serverdatetimessisetlderived-column
Abbie Waters
Abbie Waters

Reputation: 21

SSIS get only Year of a derived column that is a datetime

I need to make the FORM_YEAR the year of a derived column. The derived column is called Invoice_Document_Date and its data type is DateTime so I only want to grab the year. For my expression I wrote YEAR(dc_Invoice_Document_Date) but that doesn't seem to be right. What am I missing that will make this expression successful?

Upvotes: 2

Views: 1631

Answers (1)

Hadi
Hadi

Reputation: 37368

Make sure that the column SSIS data type is not DT_DBTIMESTAMPOFFSET or DT_DBTIMESTAMP2. Since in the official documentation they mentioned that:

The expression fails to validate when a date literal is explicitly cast to one of these date data types: DT_DBTIMESTAMPOFFSET and DT_DBTIMESTAMP2.

You can try to convert the dc_Invoice_Document_Date to DT_DATE or DT_DBDATE or DT_DBTIMESTAMP data types. As example:

YEAR((DT_DBTIMESTAMP)[dc_Invoice_Document_Date]))

You can also use DATEPART() function as follows:

DATEPART("yy",[dc_Invoice_Document_Date]))

Upvotes: 1

Related Questions