Thread: sql for difference between 2 dates

1. Member
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. Pando & Company
Join Date
Jun 2000
Location
Posts
7,447
date - date gives you the difference in days then use primary school maths you can get hours/minutes/seconds

3. 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;```

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

Chintz

5. 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;```

6. Member
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.

Chintz

7. 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.

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;```

8. svk
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 ?

9. 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;```

10. svk
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

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•