-
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
-
date - date gives you the difference in days then use primary school maths you can get hours/minutes/seconds
-
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;
-
So if I am in SQL*PLUS what would the SQL statement be?
Chintz
-
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;
-
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
-
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;
-
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
-
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;
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|