sql for difference between 2 dates

[Chintz]

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

[pando]

date - date gives you the difference in days then use primary school maths you can get hours/minutes/seconds

[gandolf989]

This might be overkill, but I put this together
based on sql I found on this site.

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;

[Chintz]

So if I am in SQL*PLUS what would the SQL statement be?

Chintz

[gandolf989]

Originally posted by Chintz
So if I am in SQL*PLUS what would the SQL statement be?

Chintz

Like this.

SELECT SYSDATE - TO_DATE('01-JAN-2005') "Day of Year" 
  FROM dual;

[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

[gandolf989]

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.

SELECT  ( SELECT max(period_date) period_date FROM period ) - 
        ( current_date + 544 ) elapsed_time
  FROM period;

[svk]

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 ?

[gandolf989]

HE is combining an aggregate and non aggregate column.

He could also do this:

SELECT max(period_date) - (current_date+544) 
  FROM period
 GROUP BY current_date;

[svk]

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