Yes, any standard Oracle functions should work.  Date1-date2 should return a number of days by default.  Here are some standard Oracle date function tutorials https://www.tutorialspoint.com/plsql/plsql_date_time.htm

If you need to do any work with the IFS calendars, consider looking at the work_time_counter_API.  This API provides methods for calculating numbers of working days between two dates or finding the next valid working day.

good info- thx.

I noticed that  TIMESTAMPDIFF() doesn’t work in IFS SQL Query Tool. What would be the equivalent to us in quick reports?

TIMESTAMPDIFF is a function in DB2 and MySQL, but it is not valid in Oracle Database.

If you want the difference in hours, take the difference in days and multiply by 24.

(lag_dt - dt) * 24 AS tsd

If you want to ignore minutes and seconds, you can truncate each to their hour before taking this difference.

(TRUNC(lag_dt, 'HH24') - TRUNC(dt, 'HH24')) * 24 AS tsd

Thx, Durette! Good to know.

Thanks for the replies.  I stopped getting notifications of replies so I apologize for the delay.

On FSM Mobile the database is SQLite so the function I used was strftime() to get the difference in seconds between two dates. rsbisho2’s reply got me on the right track even though the Oracle functions weren’t available to me.  

Thanks again!

Oracle Date and Time

Determine the interval breakdown between two dates for a DATE datatype

1 SELECT TO_CHAR(date1,'MMDDYYYY:HH24:MI:SS') date1,

2 TO_CHAR(date2,'MMDDYYYY:HH24:MI:SS') date2,

3 trunc(86400*(date2-date1))-

4 60*(trunc((86400*(date2-date1))/60)) seconds,

5 trunc((86400*(date2-date1))/60)-

6 60*(trunc(((86400*(date2-date1))/60)/60)) minutes,

7 trunc(((86400*(date2-date1))/60)/60)-

8 24*(trunc((((86400*(date2-date1))/60)/60)/24)) hours,

9 trunc((((86400*(date2-date1))/60)/60)/24) days,

10 trunc(((((86400*(date2-date1))/60)/60)/24)/7) weeks

11* FROM date_table

DATE1 DATE2 SECONDS MINUTES HOURS DAYS WEEKS

----------------- ----------------- ---------- ---------- ---------- ---------- ----------

06202003:16:55:14 07082003:11:22:57 43 27 18 17 2

06262003:11:16:36 07082003:11:22:57 21 6 0 12 1

Determine the interval breakdown between two dates for a TIMESTAMP datatype

1 SELECT time1,

2 time2,

3 substr((time2-time1),instr((time2-time1),' ')+7,2) seconds,

4 substr((time2-time1),instr((time2-time1),' ')+4,2) minutes,

5 substr((time2-time1),instr((time2-time1),' ')+1,2) hours,

6 trunc(to_number(substr((time2-time1),1,instr(time2-time1,' ')))) days,

7 trunc(to_number(substr((time2-time1),1,instr(time2-time1,' ')))/7) weeks

8* FROM date_table

TIME1 TIME2 SECONDS MINUTES HOURS DAYS WEEKS

------------------------- -------------------------- ------- ------- ----- ---- -----

06/20/2003:16:55:14:000000 07/08/2003:11:22:57:000000 43 27 18 17 2

06/26/2003:11:16:36:000000 07/08/2003:11:22:57:000000 21 06 00 12 1