I need to find the exact number of days, hours ,minutes and seconds elapsed between two given dates (Given format is in 'ddmmyyhhmiss' format)
Please can somebody advice me
I'm writing this without having the database anywhere near, so there might by some errors or typos.... BTW, I hope your given date format has HH24 instead of HH, or you have to have AM indicator somewhere in your date.
TRUNC(TO_DATE(date1,''ddmmyyhh24miss') - TO_DATE(date2,''ddmmyyhh24miss')) days,
TRUNC((TO_DATE(date1,''ddmmyyhh24miss') - TO_DATE(date2,''ddmmyyhh24miss'))*24) hours,
TRUNC((TO_DATE(date1,''ddmmyyhh24miss') - TO_DATE(date2,''ddmmyyhh24miss'))*24*60) mins,
TRUNC((TO_DATE(date1,''ddmmyyhh24miss') - TO_DATE(date2,''ddmmyyhh24miss'))*24*60*60) secs
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
The previous solution gives the number of days or hours or minutes or seconds between your two dates (e.g. if the dates are 1 hour apart, the seconds value will be 3600). If you want the difference between two dates presented as
"days-hours:minutes:seconds", try the SQL below. Note that this uses two DATE columns, d1 and d2. In your case, you will need to substitute TO_DATE(date1,'ddmmyyhh24miss') for d1 and TO_DATE(date1,'ddmmyyyyhh24miss') for d2.
select to_char( floor( d2 - d1 ) ) || '-' || to_char( to_date( to_char( floor( ( ( d2 - d1 ) - floor( d2 - d1 ) ) * 86400 ), '00000' ), 'sssss' ), 'hh24:mi:ss' ) from junk;
This is based on the fact that the difference between two DATEs returns a fractional number of days and that there are 86400 seconds in a day. So, floor(d2-d2) returns the integer number of days between the two dates, and (d2-d2)-floor(d2-d1) returns the remaining fraction of a day between the two dates. Multiplying this by 86400 gives the number of seconds. This is truncated to integer (using floor(...)) before passing it into TO_CHAR so that we don't end up with any decimal places. The resulting 5-digit number is a valid time format and so can be converted to a date using the 'sssss' format. The resulting date can be converted to HH:MM:SS using the HH24:MI:SS date picture.
Click Here to Expand Forum to Full Width