date conversion

srimitta · Post by **srimitta** » Fri May 14, 2004 7:42 am

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

nag0143 · Post by **nag0143** » Fri May 14, 2004 7:50 am

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 · Post by **nag0143** » Fri May 14, 2004 7:51 am

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)

srimitta · Post by **srimitta** » Fri May 14, 2004 8:04 am

I am trying to do conversion in transformation stage

kcbland · Post by **kcbland** » Fri May 14, 2004 8:07 am

You need quotes:

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

chulett · Post by **chulett** » Fri May 14, 2004 8:11 am

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

Code: Select all

substr(input,9,2):"/":substr(input,6,2):"/":substr(input,3,2) or
input[9,2]:"/":input[6,2]:"/":input[3,2]

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.

Code: Select all

Oconv(Iconv(YourField,"D-YMD[4,2,2]"),"D/DMY[2,2,2]")

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.

srimitta · Post by **srimitta** » Fri May 14, 2004 9:02 am

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

kcbland · Post by **kcbland** » Fri May 14, 2004 9:10 am

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:

Code: Select all

Oconv(Iconv(DSLink3.HIREDATE,"D-YMD[4,2,2]"),"D/DMY[2,2,2]")

srimitta · Post by **srimitta** » Fri May 14, 2004 9:38 am

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

chulett · Post by **chulett** » Fri May 14, 2004 10:01 am

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

Try this:

Code: Select all

Oconv(Iconv(DSLink3.HIREDATE[1,10],"D-YMD[4,2,2]"),"D/DMY[2,2,2]")

srimitta · Post by **srimitta** » Fri May 14, 2004 10:14 am

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

srimitta · Post by **srimitta** » Fri May 14, 2004 1:35 pm

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

ray.wurlod · Post by **ray.wurlod** » Fri May 14, 2004 4:53 pm

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.