Date Conversion formats

aasaif · Post by **aasaif** » Tue Dec 23, 2008 2:16 pm

Hi -
I needed help converting a date format(mm/dd/yyyy hh:mm:ss)
into (dd-MON-YY hh:mm:ss)

i think i have the second part correct
OCONV( ICONV(DSLink2.CreateDate,"D4YMD") ,"D-DMY[2,3A,2] hh:mm:ss")

any help?

chulett · Post by **chulett** » Tue Dec 23, 2008 2:35 pm

Close. Build a small 'test' routine in the Manager to check. Try:

Code: Select all

OCONV(ICONV(Field(DSLink2.CreateDate," ",1,1),"D"),"D-DMY[2,A3,2]") : Field(DSLink2.CreateDate," ",2,1)

Basically, split off the time and then stick it back on the converted date.

aasaif · Post by **aasaif** » Tue Dec 23, 2008 2:55 pm

i tried this still outputing null

OCONV(ICONV(Field(DSLink2.CreateDate,"",1,1),"D"),"D-DMY[2,3A,2] hh:mm:ss")

chulett · Post by **chulett** » Tue Dec 23, 2008 2:57 pm

Right. You need to 'convert' the date portion only and you can't include that time mask in the output conversion. So split into two, convert date, stick time back on.

aasaif · Post by **aasaif** » Tue Dec 23, 2008 3:06 pm

STill null

this is what i have in the transformer
OCONV(ICONV(Field(DSLink2.CreateDate,"",1,1),"D"),"D-DMY[2,3A,2] ")

this is the data
10/29/2008 8:31:00 AM

chulett · Post by **chulett** » Tue Dec 23, 2008 3:15 pm

Field needs a space as the second (delimiter) argument, so " " not "". And as I posted, it's "A3" not "3A" for a three letter month name.

ray.wurlod · Post by **ray.wurlod** » Tue Dec 23, 2008 6:57 pm

There is no such think as Oconv() for a timestamp. Your second argument is completely wrong. You must convert the date and time portions separately then concatenate them.

aasaif · Post by **aasaif** » Wed Dec 24, 2008 8:45 am

Can you help me with how do you parse? Look through help didnt find anything

chulett · Post by **chulett** » Wed Dec 24, 2008 8:53 am

Just like I posted. Field() can do the parsing for you here. And since your time format is different than you originally posted, you'll need to convert that as well using "MTS".

aasaif · Post by **aasaif** » Wed Dec 24, 2008 8:56 am

i place the format of the function exactly as you had it
but datastage is otuputing the internal form of the date "14913" am i missing something

chulett · Post by **chulett** » Wed Dec 24, 2008 9:02 am

I guess so. Post your latest derivation.

aasaif · Post by **aasaif** » Wed Dec 24, 2008 9:18 am

here it is

OCONV(ICONV(Field(DSLink2.CreateDate," ",1,1),"D"),"D-DMY[2,A3,2] ")

chulett · Post by **chulett** » Wed Dec 24, 2008 9:26 am

Works just fine for me with your posted data:

"10/29/2008 8:31:00 AM" -> "29-OCT-08"

aasaif · Post by **aasaif** » Wed Dec 24, 2008 9:44 am

Oh i see what i did wrong i modified the output colum in the transform to datatype of date it was varchar looks good now.

Thank you very much

Now all i got to do is something like this correct
OCONV(ICONV(Field(DSLink2.CreateDate," ",1,1),"D"),"D-DMY[2,A3,2] ") + Field(DSLink2.CreateDate," ",10,8)

chulett · Post by **chulett** » Wed Dec 24, 2008 9:53 am

Use ":" for concatenation, not "+". And you want the 2nd & 3rd "field" for the timestamp part, so Field(DSLink2.CreateDate," ",2,2) to mean "two contiguous fields starting with the 2nd field".