Dates function
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Dates function

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    hi,
    I have to find the exact time elapsed between the 2 different dates like number of days, hours,minutes and seconds.
    Can some one help me
    Badrinath

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Use simple date subtraction. This will give you the number of days between the two dates. You can then use your math skills to figure out how many hours, minutes, seconds the fractional part is. Example:
    jeff@dev815nt.us> create table date_test (d1 date, d2 date, d3 date, d4 date);

    Table created.
    1 insert into date_test values ( to_date('01/12/2001','mm/dd/yyyy'),
    2 to_date('01/13/2001 12:21:21','mm/dd/yyyy hh24:mi:ss'),
    3 to_date('01/13/2001 19:32:21','mm/dd/yyyy hh24:mi:ss'),
    4* to_date('01/15/2001 04:01:21','mm/dd/yyyy hh24:mi:ss'))
    jeff@dev815nt.us> /

    1 row created.

    jeff@dev815nt.us> commit;

    Commit complete.

    jeff@dev815nt.us> select d2 - d1 from date_test;\
    2
    jeff@dev815nt.us> select d2 - d1 from date_test;

    D2-D1
    ----------
    1.51482639

    jeff@dev815nt.us> select d4 - d2 from date_test;

    D4-D2
    ----------
    1.65277778

    jeff@dev815nt.us> select d4 - d1 from date_Test;

    D4-D1
    ----------
    3.16760417

    jeff@dev815nt.us> select sysdate - d1 from date_test;

    SYSDATE-D1
    ----------
    21.5007639

    jeff@dev815nt.us>
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    select date1 - date2 from dual;

    The above will give you number of days (it could be fractional)

    Use TRUNC to get the number of days

    Use MOD to get the number of hours, minutes, seconds etc.

    Let me know if you need more explnation ?

    - Rajeev

  4. #4
    Join Date
    Jan 2001
    Posts
    642
    RSURI,
    CAN YOU GIVE ME THE SQL IF POSSIBLE, or should I compute the hours , minutes and seconds based on the difference between dates using some plsql(For math computations)
    Badrinath

  5. #5
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    Yes, it will be much easier to compute the data difference and then use PL/SQL to compute the rest of the stuff in a PL/SQL block rather than writing one SELECT statement.
    But just for the fun of it I did write a little select sql here --

    select TRUNC(3.23) as Days , TRUNC((3.23 - TRUNC(3.23))*24) as Hours, TRUNC((((3.23 - TRUNC(3.23))*24) - TRUNC((3.23 - TRUNC(3.23))*24))*60) as Minutes,
    ((((3.23 - TRUNC(3.23))*24) - TRUNC((3.23 - TRUNC(3.23))*24))*60 - TRUNC((((3.23 - TRUNC(3.23))*24) - TRUNC((3.23 - TRUNC(3.23))*24))*60))*60 as Seconds from dual

    It shows the following result --

    DAYS HOURS MINUTES SECONDS
    --------- --------- --------- ---------
    3 5 31 12



    I am sure there will be better ways to accomplish this; this is one of them. I used "3.23" days just an example; In your case you need to replace this with "date1 - date2" or whatever your fields are.

    - Rajeev

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