Page 1 of 3
Date Conversion formats
Posted: Tue Dec 23, 2008 2:16 pm
by aasaif
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?
Posted: Tue Dec 23, 2008 2:35 pm
by chulett
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.
Posted: Tue Dec 23, 2008 2:55 pm
by aasaif
i tried this still outputing null
OCONV(ICONV(Field(DSLink2.CreateDate,"",1,1),"D"),"D-DMY[2,3A,2] hh:mm:ss")
Posted: Tue Dec 23, 2008 2:57 pm
by chulett
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.
Posted: Tue Dec 23, 2008 3:06 pm
by aasaif
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
Posted: Tue Dec 23, 2008 3:15 pm
by chulett
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.
Posted: Tue Dec 23, 2008 6:57 pm
by ray.wurlod
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.
Posted: Wed Dec 24, 2008 8:45 am
by aasaif
Can you help me with how do you parse? Look through help didnt find anything
Posted: Wed Dec 24, 2008 8:53 am
by chulett
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".
Posted: Wed Dec 24, 2008 8:56 am
by aasaif
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
Posted: Wed Dec 24, 2008 9:02 am
by chulett
I guess so. Post your latest derivation.
Posted: Wed Dec 24, 2008 9:18 am
by aasaif
here it is
OCONV(ICONV(Field(DSLink2.CreateDate," ",1,1),"D"),"D-DMY[2,A3,2] ")
Posted: Wed Dec 24, 2008 9:26 am
by chulett
Works just fine for me with your posted data:
"10/29/2008 8:31:00 AM" -> "29-OCT-08"
Posted: Wed Dec 24, 2008 9:44 am
by aasaif
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)
Posted: Wed Dec 24, 2008 9:53 am
by chulett
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".