I'm currently working on an assignment where I have to port some Oracle scripts to MS SQL. I ran into a problem at the scheduled jobs. The Oracle script looks like this:
dbms_job.submit(job =>v_job,
what =>'begin pkg_report.REFRESH_MVIEWS; end;',
next_date =>Trunc(sysdate, 'HH24')+70/1440, interval =>'Trunc(sysdate, ''HH24'')+70/1440');
dbms_job.submit(job =>v_job, what =>'begin pkg_housekeeping.cleanup_daily; end;', next_date =>trunc(sysdate)+1, interval =>'trunc(sysdate)+1+1/24');
The problem is, I don't understand what this truncing is supposed to do. I tried to replicate it in SQL Developer, played around with it a bit, most of the format strings have very obvoius outcome (YEAR, MONTH, ...), but I don't know what HH24 is supposed to do. And what's with the +70/1440, +1, +1+1/24 suffixes at the end?
I'd appreciate a little help. Thanks in advance!
TRUNC removes the time element of the current date, so the code sets the date to midnight of today (sysdate) and then adds 70/1440 of a day to it.
70/1440 of a day is 01:10 (ten past one in the morning)
+1+1/24 adds one day and 1/24th of a day, so 1am the following day