Post reply

Expression Convert Date mm to Mon

  • February 17, 2016 at 11:45 pm

    #308059

    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

    #1860268

    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