DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: LPAD and date format

  1. #1
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233

    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

  2. #2
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    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
  •  


Click Here to Expand Forum to Full Width