Hi all,

I have the following query,which diplay a gap between start_date and end_date in format : hh:mi:ss

SQL> select START_DATE, END_DATE ,lpad(trunc((END_DATE - START_DATE)*24),2,'0')||':'||
2 lpad((trunc((END_DATE - START_DATE)*24*60) - (trunc((END_DATE - START_DATE)*24)*60)),2,'0')||':
'||
3 lpad((trunc((END_DATE - START_DATE)*24*60*60) - (trunc((END_DATE - START_DATE)*24*60))*60),2,'0
') AS "Gap"
4 from nir_test;

START_DATE END_DATE Gap
---------------------- ---------------------- ----------
20/05/2004 14:50:52 28/05/2004 14:50:52 19:00:00

The gap should be 192 hours.
If i change the LPAD definition of the hours from "2" to "3" then:

SQL> col START_DATE format a22
SQL> col END_DATE format a22
SQL> col Gap format a10
SQL> select START_DATE, END_DATE ,lpad(trunc((END_DATE - START_DATE)*24),3,'0')||':'||
2 lpad((trunc((END_DATE - START_DATE)*24*60) - (trunc((END_DATE - START_DATE)*24)*60)),2,'0')||':
'||
3 lpad((trunc((END_DATE - START_DATE)*24*60*60) - (trunc((END_DATE - START_DATE)*24*60))*60),2,'0
') AS "Gap"
4 from nir_test;

START_DATE END_DATE Gap
---------------------- ---------------------- ----------
20/05/2004 14:50:52 28/05/2004 14:50:52 192:00:00

Now the format is: hhh:mi:ss

My question:
i don't want to change the definition of the Gap to hhh:mi:ss with zeros,because this kind of gap in our application has very rare possibility.

I want to change the format to : N Days,hh:mi:ss

How can i do it?

Thanks in advance,
Nir