2

I am unable to apply transformation using below code. getting error

The year function doesn't support dt_wstr.

The expression iam using is:

(DT_I4)((DT_WSTR,4)YEAR(fisc_wk_end_dt) + 
       RIGHT("0" + (DT_WSTR,2)MONTH(fisc_wk_end_dt),2) + 
       RIGHT("0" + (DT_WSTR,2)DAY(fisc_wk_end_dt),2))
Hadi
  • 36,233
  • 13
  • 65
  • 124
user3828029
  • 27
  • 6

1 Answers1

3

Problem

From the expression you mentioned, it looks like fisc_wk_end_dt column data type is string while YEAR, MONTH, DAY function's parameters must be of type date.

From the official documentation:

Syntax

YEAR(date)

Arguments

date Is a date in any date format.

Result Types

DT_I4

Possible solutions

(1) Using TOKEN() function

I will assume that fisc_wk_end_dt contains a date stored as string, as example:

1/1/2016

And you are looking to convert it to an integer `yyyyMMdd``:

20160101

Then you can use TOKEN() function to achieve that:

TOKEN(fisc_wk_end_dt,"/",3) + RIGHT("0" + TOKEN(fisc_wk_end_dt,"/",2),2) + RIGHT("0" + TOKEN(fisc_wk_end_dt,"/",1),2)

(2) Trying to parse the column to date

You can try to parse the column into DT_DATE or DT_DBTIMESTAMP types, this may works if date is stored in a format that can be parsed.

(DT_I4)((DT_WSTR,4)YEAR((DT_DBTIMESTAMP)fisc_wk_end_dt) + 
   RIGHT("0" + (DT_WSTR,2)MONTH((DT_DBTIMESTAMP)fisc_wk_end_dt),2) + 
   RIGHT("0" + (DT_WSTR,2)DAY((DT_DBTIMESTAMP )fisc_wk_end_dt),2))

References

Hadi
  • 36,233
  • 13
  • 65
  • 124