How to schedule a job on particular date

Question

How to schedule a job on particular date

srilu_bannu

Hall of Fame

Points: 3100
More actions
August 9, 2010 at 2:27 pm

#222296

Hi,
I want to schedule a job on the second day of our fiscal calendar.our fiscal calender varies from normal calender.everything will be there in our date table:
Date Table:
calenderday datetime,
fiscalmonthstart datetime,
fiscalmonthend datetime
if we give the today date in calenderday column the table will tell us when the fiscalmonthstarts and stays like that until next fiscalmonthstart...
Now, using this fiscalmonthstart how can i schedule job at the second day of the fiscalmonth.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55553 More actions · Answer 1

Perhaps you could schedule a job to run every day, and when getdate = fiscalmonthstart + 1 you can start the job with sp_start_job.

srilu_bannu Hall of Fame Points: 3100 More actions · Answer 2

I wrote this to start the job at particular day of the month.i want the getdate() to be just '2010-08-11' with out the minutes and milli seconds .i couldn't do it so i created a variable for testing and gave the date manually.this is the date table:

Date Table:

Calenderday fiscalmonthstart fiscalmonthend

2010-07-23 00:00:00 2010-06-25 2010-07-23

2010-07-24 00:00:00 2010-07-24 2010-08-20

2010-07-25 00:00:00 2010-07-24 2010-08-20

' " "

2010-08-21 00:00:00 2010-08-21 2010-09-24

This is the script:

declare @date datetime

declare @getdate-2 smalldatetime

select @getdate-2='2010-07-25 00:00:00' --- this is a temporary variable for testing

select @date=fiscalmonthstart from dbo.date where calday=@getdate

set @date= dateadd(day,1,@date)

if(@getdate=@date)

begin

exec dbo.DropConstraints -- need to drop constraints before starting the job

exec msdb.dbo.sp_start_job @job_name = 'jobname'

exec dbo.storedprocedure -- recreate the constraints

end

but when i run it in SSMS first it is not executing procedure dbo.dropconstraints and directly invoking the job which will result in failure ,can somebody help how exactly i should schedule this job and how can i get getdate() with out the minutes and seconds ...

srilu_bannu Hall of Fame Points: 3100 More actions · Answer 3

I got it by creating a stored procedure for the script and ran it in SSMS.

Anyways,my other question still remains which is how can i only get '2010-08-11' for the getdate() function instead of '2010-08-11 13:04:14.440'

Can anyone please tell me ?

srilu_bannu Hall of Fame Points: 3100 More actions · Answer 4

I found it ,The document titled “Remove seconds from datetime SQL“ gave everything i need:

if anyone having the same issue this is the syntax:

SELECT Cast(Convert(varchar, GetDate(),105) as datetime)

Thank you