Help with manipulating timestamps,intervals and dates 1

[dwhalen]

Hello,

I am porting my application over from postgresql to Oracle because I want to be able to offer my application in both postgresql and oracle. However, I am having trouble with timestamps, dates and intervals. I use these a lot in the reporting features of my application and I guess since timestamps are new to oracle they do not have as many features to work with them.

Anyway here is my SQL:

SELECT client,order_id, ( CASE WHEN ordertype=105000000 THEN 'Profile Update / Creation' END ),workbasket,task,(TO_DATE(TO_CHAR(close_date,'DD-Mon-YYYY HH:MI:SS AM'),'DD-Mon-YYYY HH:MI:SS AM') - TO_DATE(TO_CHAR(creation_date,'DD-Mon-YYYY HH:MI:SS AM'),'DD-Mon-YYYY HH:MI:SS AM')),(CASE WHEN assigned_id IS NULL THEN 'Not Assigned' WHEN assigned_id ='' THEN 'Not Assigned' ELSE assigned_id END), close_id FROM perfor_view_distinct WHERE (Org LIKE 'SVI%' OR Org LIKE 'CLI%' OR Org LIKE 'PSE%' OR Org LIKE 'BMO%') AND (workbasket = 'Entry') AND (task = 'Profile Update / Creation') AND (ordertype = '105000000') AND (chain_id LIKE 'SVI%' OR chain_id LIKE 'CLI%' OR chain_id LIKE 'PSE%' OR chain_id LIKE 'BMO%') AND (close_date BETWEEN '03-Jun-2003 12:00:00 AM' AND '03-Jun-2003 11:59:59 PM')  ORDER BY ordertype;
[\code]

The part I am interested in is: 
[code]
(TO_DATE(TO_CHAR(close_date,'DD-Mon-YYYY HH:MI:SS AM'),'DD-Mon-YYYY HH:MI:SS AM') - TO_DATE(TO_CHAR(creation_date,'DD-Mon-YYYY HH:MI:SS AM'),'DD-Mon-YYYY HH:MI:SS AM'))
[\code]

I know that this is a bit messy but this seems to what I have to do to manipulate the timestamps like dates.  Here is the same thing except with an AVG tacked on for a summary report:

[code]
AVG((TO_DATE(TO_CHAR(close_date,'DD-Mon-YYYY HH:MI:SS AM'),'DD-Mon-YYYY HH:MI:SS AM') - TO_DATE(TO_CHAR(creation_date,'DD-Mon-YYYY HH:MI:SS AM'),'DD-Mon-YYYY HH:MI:SS AM')))
[\code]

The first result comes back with:
7.03097222222222222222222222222222222222

Is there anyway to format what comes back?  I suppose I could use a substring but there should be a way to specify precision?

Any help?

Thanks

 

[jee]

Your result is a number of days (you probably knew that). Since your result is a number, you can use the ROUND function to limit the result to a lower precision

for example:

select round (7.03097222222222222222222222222222222222, 3) from dual;

results in:

7.031

 

[dwhalen]

Thanks that works perfect. I am slowly getting to know all the Oracle specfic functions.

Thanks for you help

Later