DTS transfer form AS400 to change the date data

[dwight1]

Hi,

I have a DTS that transfers the data from AS400 TO SQL. In AS400 the date is stored as 92305 for Sept 23 05 and 192305 for Sept 19 05. Essentially there are five and six digits.

Now, I have a substrings that change this into date field.
Example:
"CASE WHEN len(LDEPD6) = 6 THEN substring(LDEPD6, 1, 2) + '/' + substring(LDEPD6, 3, 2) + '/' + substring(LDEPD6, 5, 2) WHEN len(LDEPD6)
= 5 THEN substring(LDEPD6, 1, 1) + '/' + substring(LDEPD6, 2, 2) + '/' + substring(LDEPD6, 4, 2) END AS DateLastDeposit"


My question is, can i change the data in the DTS itself. Right now i get the data in sql and then change it using the above code.

I tried to put it in the sql code of DTS. it says the field is too long to calculate.

Any suggestions

Thanks

Dwight

 

[DrSql]

Can you execute the Script from QA then you should be able execute it. Change the fields as you wish and excute in QA, the you can past in SQL Query in transfomation task.

[tt]
Select Col1, Col2,Col3,
DateLastDeposit=
CASE
WHEN len(LDEPD6) = 6 THEN substring(LDEPD6, 1, 2) + '/' + substring(LDEPD6, 3, 2) + '/' + substring(LDEPD6, 5, 2)
WHEN len(LDEPD6) = 5 THEN substring(LDEPD6, 1, 1) + '/' + substring(LDEPD6, 2, 2) + '/' + substring(LDEPD6, 4, 2) END ,
Col5,Col6 from MY_Table

[/tt]



Dr.Sql
Good Luck.