mandymag.com

This posting is primarily for me, however I will share it with you. 

Original Field

Numeric calculation that I want to pretty up into currency looking data while still performing a calculation.  (pardon my non-techie layman jargon, it’s the only way I can understand it myself).

Converted String

select LTRIM(TO_CHAR(pay_rate_field, ‘$9.99′)), LTRIM(TO_CHAR(total_award_amt_field, ‘$9,999.99′)), DECODE(LTRIM(TO_CHAR((total_award_amt_field- pay_rate_field), ‘$9,999.99′)),NULL,TO_CHAR(total_award_amt_field, ‘$9,999.99′),(LTRIM(TO_CHAR((total_award_amt_field - pay_rate_field), ‘$9,999.99′)))) INTO @Pay_rate, @Award_Amount, @Amt_Remaining

This next one is a sheer work of genius that I would have never figured out on my own.  Thanks Matt and Jon!

Original Field  

Numeric field that is a unix timestamp that I wanted to convert to a text looking date.  So essenitially I needed to take a field that looked like this:

1105980125710 

and make it a string that looks like this:

17-JAN-2005.

Converted String

to_char(new_time(unixts_to_date(trunc(timestamp_field/1000)), ‘GMT’,'EDT’),’DD-MON-YYYY’)

tags: pl/sql currency format, unix timestamp convert