-
LPAD and date format
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
-
Hey all,
I've alreadt solved my problem.
The select statement should be:
select trunc(END_DATE - START_DATE)||' Days,'||
lpad((trunc((END_DATE - START_DATE)*24)-trunc(END_DATE - START_DATE)*24),2,'0')||':'||
lpad((trunc((END_DATE - START_DATE)*24*60) - (trunc((END_DATE - START_DATE)*24)*60)),2,'0')||':'||
lpad((trunc((END_DATE - START_DATE)*24*60*60) - (trunc((END_DATE - START_DATE)*24*60))*60),2,'0') AS "Gap"
from nir_test;
Regards,
Nir
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|