Hello,
Are there any script functions for working with dates and times?
I’d like to work with the date and time as an integer so I can get the difference in hours between two date variables. I can do Date1 - Date2 but the result is another date. I’m just wanting the hours.
Something like JavaScripts Date.getTime() would be very helpful.
Best answer by rsbisho2
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.
+1Yes, 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?
+19TIMESTAMPDIFF 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 tsdIf 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
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!
+7Oracle 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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
