Last working day of the month

[RobNisbet]

Hi, I am trying to run a job on the last working day of the month - I currently use
trunc(last_day(sysdate))+18/24
as the code, which finds the last day of the month fine. The problem occurs when the last day of the month is a Saturday or Sunday - if that occurs, I need to then bring the job forward forward to the Friday night.

I have tried using next_day with little success, and am stuck! I'm sure people need to set this up all the time ....I just can't seem to find how!
Thanx
Rob

 

[lewisp]

If your system has Monday as day 1, use

[tt]decode(To_Char(trunc(last_day(sysdate))+18/24,'D'),
7,trunc(last_day(sysdate) - 2)+18/24,
6,trunc(last_day(sysdate) - 1)+18/24,
trunc(last_day(sysdate))+18/24)[/tt]

otherwise if Sunday = 1 use

[tt]decode(To_Char(trunc(last_day(sysdate))+18/24,'D'),
1,trunc(last_day(sysdate) - 2)+18/24,
7,trunc(last_day(sysdate) - 1)+18/24,
trunc(last_day(sysdate))+18/24)[/tt]

 

[RobNisbet]

Ah, clever. Thanks!

 

[RobNisbet]

Thing is, cannot use decode in the dbms_job.isubmit.

Any ideas without using decode ?


ORA-06550: line 2, column 53:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement
ORA-06550: line 2, column 2:

 

[lewisp]

[tt]DECLARE
offset NUMBER;
daynum NUMBER := To_Char(trunc(last_day(sysdate))+18/24,'D');
BEGIN
IF daynum = 7
THEN
offset := 2;
ELSIF daynum = 6
THEN
offset := 1
ELSE
offset := 0;
END IF;
--
var := trunc(last_day(sysdate) - offset)+18/24;
END;[/tt]

 

[sem]

You may create some stored function and use it.

Regards, Dima

 

[RobNisbet]

Hi, will that work inside a dbms_job command ? At the moment I use

exec dbms_job.isubmit(1,my_code;',(trunc(last_day(sysdate))+18/24), 'trunc(last_day(sysdate))+18/24',false);

I think that it needs to be a single line function based statement (but annoyingly not decode!)

Sem, so you mean create a function like the code from Lewis which returns the date I need, then call the function from the job command eg.

exec dbms_job.isubmit(1,my_code;',my_function, 'my_function',false);

 

[sem]

Exactly. Of course if you like puzzles, you may create some statement, possibly using SIGN(), MOD() and/or some other functions, but this will be apparently less readable solution.

Regards, Dima