February 17, 2016 at 11:45 pm
Hi
In the derived column editor I am trying to change the month part of the dinvoicedate (UK Format 25/01/2016) from numerical to text e.g. 25/01/2016 to 25/Jan/2016
At the start of work flow I convert Dinvoicedate to DT_DATE or DB_DBDate for easier conversion but it doesn't work. I think it is because i need it to only happen when the line id is equal to 1 and column 'division' is equal to a string 'xxxx'.
1. (dLineID) == 1 && dDivisionBuyer == "somestring" ? datepart("day", dInvoiceDate) + datepart("month", dInvoiceDate) + datepart("Year",dInvoiceDate) : dInvoiceDate
or
2.(dLineID) == 1 && dDivisionBuyer == "somestring" ? SUBSTRING(dInvoiceDate,1,2) + "/" + (MONTH((DT_DBDATE)(dInvoiceDate)) == 1 ? "Jan" : MONTH((DT_DBDATE)(dInvoiceDate)) == 2 ? "Feb" : MONTH((DT_DBDATE)(dInvoiceDate)) == 3 ? "Mar" : MONTH((DT_DBDATE)(dInvoiceDate)) == 4 ? "Apr" : MONTH((DT_DBDATE)(dInvoiceDate)) == 5 ? "May" : MONTH((DT_DBDATE)(dInvoiceDate)) == 6 ? "Jun" : MONTH((DT_DBDATE)(dInvoiceDate)) == 7 ? "Jul" : MONTH((DT_DBDATE)(dInvoiceDate)) == 8 ? "Aug" : MONTH((DT_DBDATE)(dInvoiceDate)) == 9 ? "Sep" : MONTH((DT_DBDATE)(dInvoiceDate)) == 10 ? "Oct" : MONTH((DT_DBDATE)(dInvoiceDate)) == 11 ? "Nov" : MONTH((DT_DBDATE)(dInvoiceDate)) == 12 ? "Dec" : "NA") + "/" + (DT_WSTR,4)(LEN(TRIM(dInvoiceDate)) == 0 ? NULL(DT_WSTR,10) : SUBSTRING(dInvoiceDate,7,4)) : dInvoiceDate
1. Error message, the data types DT_I4 and DT_DBDATE are incompatible for the conditional operator. The operand cannot implicitly cast into compatible types for the conditional operation.
2. Error Message, the function substring does not support the data type DT_DBDATE for paramter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function.
Thanks for any assistance.
February 22, 2016 at 9:54 pm
I resolved it using the code below, you cant use the derived column replace function and datepart, so i had to make it as a new column and in a new data flow task.
(dLineID) == 1 && dDivisionBuyer == "Publicis Jimenezbasic" ? RIGHT("0" + (DT_STR,2,1252)DATEPART("dd",dInvoiceDate),2) + "/" + (MONTH((DT_DBDATE)(dInvoiceDate)) == 1 ? "Jan" : MONTH((DT_DBDATE)(dInvoiceDate)) == 2 ? "Feb" : MONTH((DT_DBDATE)(dInvoiceDate)) == 3 ? "Mar" : MONTH((DT_DBDATE)(dInvoiceDate)) == 4 ? "Apr" : MONTH((DT_DBDATE)(dInvoiceDate)) == 5 ? "May" : MONTH((DT_DBDATE)(dInvoiceDate)) == 6 ? "Jun" : MONTH((DT_DBDATE)(dInvoiceDate)) == 7 ? "Jul" : MONTH((DT_DBDATE)(dInvoiceDate)) == 8 ? "Aug" : MONTH((DT_DBDATE)(dInvoiceDate)) == 9 ? "Sep" : MONTH((DT_DBDATE)(dInvoiceDate)) == 10 ? "Oct" : MONTH((DT_DBDATE)(dInvoiceDate)) == 11 ? "Nov" : MONTH((DT_DBDATE)(dInvoiceDate)) == 12 ? "Dec" : "NA") + "/" + (DT_STR,4,1252)DATEPART("yyyy",dInvoiceDate) : RIGHT("0" + (DT_STR,2,1252)DATEPART("dd",dInvoiceDate),2) + "/" + RIGHT("0" + (DT_STR,2,1252)DATEPART("mm",dInvoiceDate),2) + "/" + (DT_STR,4,1252)DATEPART("yyyy",dInvoiceDate)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply