DSXchange

Hi Gurus,

I am trying to convert date format (yyyy-mm-dd TO dd/mm/yy). can i define the conversion in constraint or i have to write a routine, please help me with conversion procedure.

Thankyou
ms

There are many ways to do this one way is to use

in derivation use

substr(input,9,2):/:substr(input,7,2):/:substr(input,1,2)


HTH
Nag

nag0143 wrote:There are many ways to do this one way is to use

in derivation use

substr(input,9,2):/:substr(input,7,2):/:substr(input,1,2)


HTH
Nag

correction
substr(input,9,2):/:substr(input,7,2):/:substr(input,3,2)

I am trying to do conversion in transformation stage

You need quotes:

link.column[9,2]:"/":link.column[7,2]:"/":link.column[3,2]

Actually, one position is off by one and you need quotes:


Only substring dates if you have complete confidence in their validity. You can also use Oconv and Iconv to convert between formats and validate the dates at the same time.
If you want to add the status checking bits, do this in routine. Separate the two functions and use the Status function to check the success of the conversion. This is all in your online help, or many examples can be found here by searching.

Hi,
i tried the below code in the constrain to change the date format
DSLink3.HIREDATE=Oconv(Iconv(DSLink3.HIREDATE,"D-YMD[4,2,2]"),"D/DMY[2,2,2]")

but i am getting the following error:
Attempting to Cleanup after ABORT raised in stage oraseqdate..Transformer_1
what exactly i have to use, constrain or routine to change Date format.
please explain me with the sample code.


Thanks

srimitta wrote:Hi,
i tried the below code in the constrain to change the date format
DSLink3.HIREDATE=Oconv(Iconv(DSLink3.HIREDATE,"D-YMD[4,2,2]"),"D/DMY[2,2,2]")

Just use:

Hi thanks for reply,
I tried your CODE but i am getting same error.
Input date formate is TIMESTAMP(1980-12-17 00:00:00) , I want to convert to 17/12/1980.
Thankyou
ms

It helps when you let people know important things like that up front.

Try this:

HI,
I am getting same error Attempting to Cleanup after ABORT raised in stage oraseqdate..Transformer_1

Hi,
My source is ODBC from oracle table with column name HIREDATE,format is Timestamp (1980-12-17 00:00:00). Here iam trying to convert date fromat to (17/12/1980), for this conversion i used CODE: Oconv(Iconv(DSLink3.HIREDATE[1,10],"D-YMD[4,2,2]"),"D/DMY[2,2,2]") In CONSTRAINT and in DERIVATION too, and populate flat table using SEQUENTIAL STAGE , But iam getting error: Attempting to Cleanup after ABORT raised in stage oraseqdate..Transformer_1 .
Please help me where to use this code (in derivation, constraint or routine)to get desired output format.
THANKYOU
MS

ODBC can automatically convert to internal date format, so that your transformations would not require the Iconv function.

To learn more about date conversions with Iconv and Oconv, go here.