I a column which has dates in yyyymdd and yyyymmdd format. I want to convert the yyyymdd to yyyymmdd.

for example I have dates 2022520 and 20220520. how can I convert 202250 to 20220520 using SSIS?

I tired the following

1. using len I counted the number of characters
2. then used if condition to add 0 before 5 in "2022520". [Character count]==7 ? "0" Substring([Extract Date],5,2) : [Extract Date]
3. result of this expression 052

CodePudding user response：

Better it would be when you dsave dates in a proper date clumn in the right format

Eveything else is costly

CREATE TABLE test
    ([ExtractDate] varchar(8))
;
    
INSERT INTO test
    ([ExtractDate])
VALUES
    ('2022205'),
    ('20221231'),
    ('2021115')
;

3 rows affected

SELECT LEFT([ExtractDate],4)  
  RIGHT('00'   LEFT(
SUBSTRING([ExtractDate], 5, LEN([ExtractDate]))
,LEN(SUBSTRING([ExtractDate], 5, LEN([ExtractDate]))) -2),2)
    RIGHT([ExtractDate],2)
FROM test

fiddle

CodePudding user response：

You can use this expression:

Left([Extract Date], 4)   Right('00'   Right([Extract Date], Len([Extract Date]) - 4), 4)

Example:

2022520     20220520
20221231    20221231
20220520    20220520