Convert 'YYYY-MM-DD hh:nn:ss' format to 'YYYY-MON-DD' in PX

dw_prasanta · 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 · Post by **ray.wurlod** » Tue Jul 24, 2007 2:16 pm

TimestampToString, StringToDate, DateToString - what data type is your output column (date or string)?

dw_prasanta · 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 · 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 · 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 · 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 · 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 · 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 · Post by **balajisr** » Wed Jul 25, 2007 1:52 am

3 digit month acronyms is undocumented.

ray.wurlod · Post by **ray.wurlod** » Wed Jul 25, 2007 3:42 am

... or use lookup_string_from_int16() function in the Modify stage.

dw_prasanta · 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