sql for difference between 2 dates
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: sql for difference between 2 dates

  1. #1
    Join Date
    Dec 1999
    Posts
    217

    sql for difference between 2 dates

    I am trying to get a difference between 2 dates in a SQL statement. I know there is easy way of doing this. Can any of you help?

    Thanks in davance,
    Chintz

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    date - date gives you the difference in days then use primary school maths you can get hours/minutes/seconds

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    This might be overkill, but I put this together
    based on sql I found on this site.

    Code:
    CREATE OR REPLACE FUNCTION 
       elapsed_time (p_start_date IN DATE, p_end_date IN DATE )
       RETURN VARCHAR2
    IS
        v_diff_in_days      NUMBER;
        v_days              NUMBER;
        v_hours             NUMBER;
        v_minutes           NUMBER;
        v_seconds           NUMBER;
        v_total_hours       NUMBER;
        v_total_minutes     NUMBER;
        v_total_secs        NUMBER;
        v_formatted_string  VARCHAR2(200);
    BEGIN
        v_diff_in_days  := p_end_date - p_start_date;
        v_days          := TRUNC(v_diff_in_days);
        v_hours         := (v_diff_in_days-FLOOR(v_diff_in_days)) * 24;
        v_total_hours   := TRUNC(v_hours);
        v_minutes       := (v_hours-FLOOR(v_hours)) * 60;
        v_total_minutes := TRUNC(v_minutes);
        v_seconds       := (v_minutes-FLOOR(v_minutes)) * 60;
        v_total_secs    := TRUNC(v_seconds);
        CASE
        WHEN v_days          > 0 THEN
           v_formatted_string := v_days          || ' Days, '       ||
                                v_total_hours    || ' Hours, '      ||
                                v_total_minutes  || ' Minutes and ' ||
                                v_total_secs     || ' Seconds ';
        WHEN v_total_hours   > 0 THEN
           v_formatted_string := v_total_hours   || ' Hours, '      ||
                                v_total_minutes  || ' Minutes and ' ||
                                v_total_secs     || ' Seconds ';
        WHEN v_total_minutes > 0 THEN
           v_formatted_string := v_total_minutes || ' Minutes and ' ||
                                v_total_secs     || ' Seconds ';
        ELSE
           v_formatted_string := v_total_secs    || ' Seconds ';
        END CASE;
       RETURN v_formatted_string;
    END elapsed_time;
    this space intentionally left blank

  4. #4
    Join Date
    Dec 1999
    Posts
    217
    So if I am in SQL*PLUS what would the SQL statement be?

    Chintz

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    Originally posted by Chintz
    So if I am in SQL*PLUS what would the SQL statement be?

    Chintz
    Like this.

    Code:
    SELECT SYSDATE - TO_DATE('01-JAN-2005') "Day of Year" 
      FROM dual;
    this space intentionally left blank

  6. #6
    Join Date
    Dec 1999
    Posts
    217
    Specifically, what I am trying to do is:
    I have a table 'PERIOD' I want to subtract (current_date + 544 days) from the largest date in the 'period_date' column.

    Something like :
    select max(period_date) - (current_date+544) from period;

    This SQL statement is not correct. I am looking for the syntax for this.

    Thanks in advance,
    Chintz

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    Originally posted by Chintz
    Specifically, what I am trying to do is:
    I have a table 'PERIOD' I want to subtract (current_date + 544 days) from the largest date in the 'period_date' column.

    Something like :
    select max(period_date) - (current_date+544) from period;

    This SQL statement is not correct. I am looking for the syntax for this.

    Thanks in advance,
    Chintz
    Have you tried something like this? However, this could yiled a negative number.

    Code:
    SELECT  ( SELECT max(period_date) period_date FROM period ) - 
            ( current_date + 544 ) elapsed_time
      FROM period;
    this space intentionally left blank

  8. #8
    Join Date
    Jul 2000
    Posts
    521
    Why is your original statement not working ? It should work. What error are you getting ?

    May be it is not giving you the o/p that you want ?
    svk

  9. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    HE is combining an aggregate and non aggregate column.

    He could also do this:

    Code:
    SELECT max(period_date) - (current_date+544) 
      FROM period
     GROUP BY current_date;
    this space intentionally left blank

  10. #10
    Join Date
    Jul 2000
    Posts
    521
    Sample :
    SQL> create table dt (d date);

    Table created.

    SQL> insert into dt select sysdate-rownum from dba_tables where rownum<101;

    100 rows created.

    SQL> commit;

    Commit complete.

    SQL> select max(d)-(sysdate+10) from dt;

    MAX(D)-(SYSDATE+10)
    -------------------
    -11.000162
    svk

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