Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.
Moderators: chulett , rschirm , roy
dw_prasanta
Participant
Posts: 12 Joined: Mon Nov 27, 2006 1:28 am
Post
by dw_prasanta » Tue Jul 24, 2007 5:02 am
Hi All,
Could anyone plese help me how to convert 'YYYY-MM-DD hh:nn:ss'(Timestamp) format to 'YYYY-MON-DD' in PX by using any pre-defined function?
Thanks in Advance.
Regards,
Prasanta
ray.wurlod
Participant
Posts: 54607 Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Post
by ray.wurlod » Tue Jul 24, 2007 2:16 pm
TimestampToString, StringToDate, DateToString - what data type is your output column (date or string)?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
dw_prasanta
Participant
Posts: 12 Joined: Mon Nov 27, 2006 1:28 am
Post
by dw_prasanta » Wed Jul 25, 2007 1:13 am
My input data type is Timestamp. But I am not able to figure out which function should I use to get in 'YYYY-MON-DD' format.
Could you please help out in this?
Maveric
Participant
Posts: 388 Joined: Tue Mar 13, 2007 1:28 am
Post
by Maveric » Wed Jul 25, 2007 1:20 am
"'YYYY-MON-DD' format" as in 2007-jan-12 if the input is 2007-01-12?
convert the timestamp to string and get the month using field or substring function. read this as integer and compare it with months as if Svar =01 then Jan. and so on till 12. now concatenate this in the required formate.
dw_prasanta
Participant
Posts: 12 Joined: Mon Nov 27, 2006 1:28 am
Post
by dw_prasanta » Wed Jul 25, 2007 1:24 am
Thanks a lot...for the solution.
Is there any function in PX which can handle this?
Regards,
Prasanta
ArndW
Participant
Posts: 16318 Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:
Post
by ArndW » Wed Jul 25, 2007 1:27 am
No, there is no single function call which will do this, which is why Maveric proposed his solution. You can get the month number then do a single
Code: Select all
FIELD('JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC',',',{month number})
to get the month string.
balajisr
Charter Member
Posts: 785 Joined: Thu Jul 28, 2005 8:58 am
Post
by balajisr » Wed Jul 25, 2007 1:30 am
The below code converts the given timestamp into string containing date in the format 'YYYY-MON-DD'.
Code: Select all
DateToString(TimestampToDate(inLink.Col),'%yyyy-%mmm-%dd')
e.g Timestamp '2007-07-07 00:33:28' will be converted to '2007-jul-07'
Is this what you want?
ArndW
Participant
Posts: 16318 Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:
Post
by ArndW » Wed Jul 25, 2007 1:36 am
I don't know if that is what the poster wanted, but I didn't realize that PX would supply the 3 digit month acronyms {I wonder if PX handles locales as well...}
balajisr
Charter Member
Posts: 785 Joined: Thu Jul 28, 2005 8:58 am
Post
by balajisr » Wed Jul 25, 2007 1:52 am
3 digit month acronyms is undocumented.
ray.wurlod
Participant
Posts: 54607 Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Post
by ray.wurlod » Wed Jul 25, 2007 3:42 am
... or use lookup_string_from_int16() function in the Modify stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
dw_prasanta
Participant
Posts: 12 Joined: Mon Nov 27, 2006 1:28 am
Post
by dw_prasanta » Wed Jul 25, 2007 3:56 am
Thanks a lot..The problem is solved by using the conversion:-
DateToString(TimestampToDate(inLink.Col),'%yyyy-%mmm-%dd')
Actually I need this to populate in my Error message....
Thanks a lot once again... :D