date help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: date help

  1. #1
    Join Date
    Jun 2008
    Posts
    38

    date help

    i have a table called std_detail.I m geting the max and min date from the table and then finding the difference b/w dates:

    ENAME TIME_VALUE
    naveed 09/10/2008 08:30:50
    amit 10/30/2008 00:00:00
    raja 11/17/2008 03:30:50
    fasi 10/25/2008 01:30:50
    yuvi 06/27/2008 17:55:06
    -----------------------------------------------

    select
    (select to_char(max(time_value),'dd/mm/RR HH:MI:SS') from std_detail) -
    (select to_char(min(time_value),'dd/mm/RR HH:MI:SS') from std_detail) "Days diff"
    from dual

    -----------------------------------------------------------------

    the above command not working showing ORA-01722.



    --------------------------------------------------------------------------
    if i use this sql :

    select
    (select to_date(max(time_value),'dd/mm/yyyy hh:mi:ss') from std_detail) -
    (select to_date(min(time_value),'dd/mm/yyyy hh:mi:ss') from std_detail) "Days diff"
    from DUAL


    above sql gives only the date diff but not the hour min diff alongside date
    Last edited by ali560045; 07-01-2008 at 06:31 AM.

  2. #2
    Join Date
    Feb 2004
    Location
    UK
    Posts
    56
    How about :

    select to_char(max(time_value),'dd/mm/RR HH:MI:SS'),
    to_char(min(time_value),'dd/mm/RR HH:MI:SS'),
    max(time_value) - min(time_value)
    from std_detail

  3. #3
    Join Date
    Jun 2008
    Posts
    38
    thanks this is working .

    but to be presise i want in days-hour-min-sec format

    for e.g.

    143 days-01hour-23min-34sec
    -------------------------------------------------------

  4. #4
    Join Date
    Jun 2008
    Posts
    38
    i got the below sql

    select ( max(time_value) - min(time_value))
    DAY(9) TO SECOND FROM std_detail;

    output: +142 09:35:44.000000


    ----------------------------------------------------

    min(time_value)---------> 27/06/2008 17:55:06

    max(time_value)---------> 17/11/2008 03:30:50

    result ( max(time_value) - min(time_value)----> 142 09:35:44.000000
    ------------------------------------------------------------------------

    got the answer now. thanks ever1 for ur help........
    Last edited by ali560045; 07-01-2008 at 06:55 AM.

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