2

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?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Abbie Waters
  • 21
  • 2
  • Why is the data type of the derived column datetime? – billinkc Apr 14 '20 at 19:57
  • The column it is getting mapped to requires a datetime data type. – Abbie Waters Apr 15 '20 at 14:15
  • The target data type is a `datetime`. The result of a `year` operation will be an integer. So, you need to something to make the year number into a valid datetime data type. Typically, I see people peg this to a particular point in time - midnight on January 1 or 11:59:59.997 December 31 but your business requirements might be different. I have a client where the year starts April 1. So, what's the business need here? Once we know that, then it's a matter of building a string to represent the day and month along with the derived year – billinkc Apr 15 '20 at 21:41

1 Answers1

1

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]))
Hadi
  • 36,233
  • 13
  • 65
  • 124