| First day of month DBMS_JOB [message #42387] |
Wed, 26 March 2003 19:57  |
Jay
Messages: 127
Registered: October 1999
|
Senior Member |
|
|
I have two questions.
Question 1.
Is the interval below correct to use to schedule a job to run on the first day of each month?
trunc(add_months(sysdate,1) + 1)
Question 2
I'll be using the query below as part of my "WHAT" in my DBMS_JOB.SUBMIT. Basically, this query should insert into table1 all the records starting from the first day of the previous month to the last day of the previous month. So, lets say the previous month was Febuary, and today is March 01, 2003. The query below should get records with a start_date of
2003-02-01 12:00:00 AM to 2003-02-28 11:00:00 PM. I'm not too sure if the last "AND" statement is correct though. Could anyone tell me if the last line of SQL is correct? Again, this job will be schedhuled to run the query below on the first day of each month.
Insert into TABLE1
Select *
From TABLE2
Where start_time >= trunc(add_months(sysdate,-1)) and
Start_time < trunc(last_day(add_months(sysdate, -1));
Thanks,
Jay
|
|
|
|
| Re: First day of month DBMS_JOB [message #42391 is a reply to message #42387] |
Thu, 27 March 2003 02:12  |
utsav
Messages: 94
Registered: March 2003
|
Member |
|
|
Hey,
Well i dont know how u r execting the query but executing this
select trunc(add_months(sysdate,-1)) from dual will not give me the first day of last month.
use this,
select
to_date('01' ||
to_char(add_months(sysdate,-1),'mon')||
to_char(add_months(sysdate,-1),'yyyy')
) from dual;
this will indeed change the condition in the second question
to
Where start_time >= trunc(to_date('01' ||
to_char(add_months(sysdate,-1),'mon')||
to_char(add_months(sysdate,-1),'yyyy')
) ) and
Start_time < trunc(last_day(add_months(sysdate, -1));
In case of any problems do let me know.
|
|
|
|
| Re: First day of month DBMS_JOB [message #42405 is a reply to message #42387] |
Thu, 27 March 2003 11:27 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
The NEXT and the INTERVAL would be:
trunc(add_months(sysdate, 1), 'mm')
The most generic WHERE clause to return all rows for the previous month, regardless of when in the next month we run the query, is:
insert into table1
select *
from table2
where start_time between trunc(add_months(sysdate, -1), 'mm')
and trunc(sysdate, 'mm') - (1/24/60/60);
This includes up to 11:59:59 on the last day of the month. If you really mean up to 11:00:00, you can easily change that substraction to just (1/24).
Yes, we know the job is running on the first of the month, but the query is flexible so that it runs correctly on the first or the last of the month.
|
|
|
|
| Re: First day of month DBMS_JOB [message #42409 is a reply to message #42387] |
Thu, 27 March 2003 14:46 |
question911
Messages: 13
Registered: March 2003
|
Junior Member |
|
|
Hi Todd,
As always your replies have been very helpful.
In regards to question 2, whats the difference when you use a >= <= as oppose to a "BETWEEN"? Is there an advantage of using one over the other? Is there less over head on the database when you use a "Between"?
Thanks,
Jay
|
|
|
|
| Re: First day of month DBMS_JOB (Another way) [message #45369 is a reply to message #42387] |
Sat, 06 March 2004 20:04 |
Julian Ke
Messages: 2
Registered: March 2004
|
Junior Member |
|
|
|
In the Control Panel of MicroSoft Windows, Open the "schedule" , and add the query as a task, and then set the time as you want. (Before doing this, creat a shortcut point to your query and put it some where, and then you can select the shortcut as a task in "schedule" .)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
