0

I am facing issue while placing "-" in a varchar datatype. . Need is after last two digits we need to put "-" and then again after two digits and so on.

Input String is- 21220 Output String- 2-12-20

Or,

Input String- 311220 Output String- 31-12-20

Can anyone help me on this ?

Benzi
  • 398
  • 6
  • 14
Bibhudatta Hotta
  • 1
  • 1

2 Answers2

0

I would go with a script component for string manipulation because it's much easier to do and it's more readable.

1) Add a script component as a transformation

2) Under input columns, check your date column (I called this myDate in the example below)

3) Under Inputs and Outputs, under Output 0, add a column for the new date, called formattedDate below

4) In the script, modify the Input0_ProcessInputRow method. Before parsing the string, make sure it's not null. Then pad it with a "0" on the left to insure that we always have a length of 6. Parse, the date elements and set the value for the new column.

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    if (!Row.mydate_IsNull)
    {
        var paddedDate = Row.mydate.PadLeft(6, '0');
        var day = paddedDate.Substring(0, 2);
        var month = paddedDate.Substring(2, 2);
        var year = paddedDate.Substring(4, 2);
        Row.formattedDate = $"{day}-{month}-{year}";
    }
}
Mark Wojciechowicz
  • 4,287
  • 1
  • 17
  • 25
0

(DT_WSTR, 2)(DT_I4)LEFT(RIGHT("0" + @[User::date], 6), 2) + "-" + SUBSTRING(RIGHT("0" + @[User::date], 6), 3, 2) + "-" + RIGHT(@[User::date] , 2)

codefreak
  • 1
  • 2