Re: DBMS_JOB
- From: Jared Still <jkstill@xxxxxxxxx>
- To: timhall1@xxxxxxxxx
- Date: Wed, 2 Mar 2005 22:56:44 +0000
Very interesting Tim.
Here's a test of it.
alter session set nls_date_format = 'mm/dd/yyyy hh24:mi';
declare
v_date date;
v_test_date date;
type date_t is table of date index by binary_integer;
v_date_table date_t;
begin
v_date_table(1) := to_date('03/02/2005 00:29');
v_date_table(2) := to_date('03/02/2005 00:30');
v_date_table(3) := to_date('03/02/2005 00:31');
v_date_table(4) := to_date('03/02/2005 12:29');
v_date_table(5) := to_date('03/02/2005 12:30');
v_date_table(6) := to_date('03/02/2005 12:31');
for i in v_date_table.first .. v_date_table.last
loop
dbms_output.put('Test Date: ' || v_date_table(i));
SELECT
CASE WHEN TO_CHAR(v_date_table(i)+(1/48),'HH24') BETWEEN '01' AND '12'
THEN (TRUNC(v_date_table(i))+(25/48))
ELSE (TRUNC(v_date_table(i) -(1/24))+(49/48))
END
into v_test_date
from dual;
dbms_output.put_line(' Next Date: ' || v_test_date);
end loop;
end;
/
On Wed, 2 Mar 2005 13:32:47 -0800, Tim Hall <timhall1@xxxxxxxxx> wrote:
> I don't have time to test this, or fix parentheses errors on my CASE
> statement, etc., but how about something like:
>
> (CASE WHEN TO_CHAR(SYSDATE+(1/48),'HH24') BETWEEN '01' AND '12' THEN
> (TRUNC(SYSDATE)+(25/48))
> ELSE (TRUNC(SYSDATE -(1/24))+(49/48)) END)
>
> So if the current time is between 00:30:00 and 12:29:59, then the job
> runs at 12:30 in the afternoon today,
> else if the current time is not between 00:30:00 and 12:29:59, then
> the job runs at 12:30 at night (same day if current time is between
> 00:00:00 and 00:29:59, next day if current time is between 12:30:00
> and 23:59:59)
>
> Hope this helps -- Tim
>
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
//www.freelists.org/webpage/oracle-l
Other related posts:
