-1

I would like to tun the Stored Procedures on the following dates.

1st sept 2020    
1st Nov 2020    
1st Dec 2020    
1st Feb 2021    
1st March 2021    
1st May 2021    
1st june 2021    
1st august 2021

For that I have put the following code in the SQL Server Agent job

declare @dt datetime = getdate();

declare @time varchar(10) = convert(varchar(10), @dt, 108);


if ((( @dt = '2020-09-01') or
     (  @dt = '2020-11-01' )  or
     (  @dt = '2020-12-01' )  or
     (  @dt = '2021-02-01' )  or
     (  @dt = '2021-03-01' )  or
     (  @dt = '2021-05-01' )  or
     (  @dt = '2021-06-01' )  or
     (  @dt = '2020-08-07' ) ) and
     ( @time between '00:00:00' and '23:59:59'))

begin

 DECLARE @RC int

 EXECUTE @RC = [abc].[dbo].[sp1] 

end

else -- out of allowed date range

begin
      raiserror ('Not in the allowed date range', 15, 1);
end

As today is 7th of August 2020. The if condition has today's date but still its not executing the SP, instead going into the else block and raises error. Any specific thing do i need to change?

1
  • What happens if you output the value for @dt in the code? Add a simple PRINT @dt to the ELSE condition (and possibly in the IF too).
    – John K. N.
    Aug 11 '20 at 8:25
1

Start by using DATE datatype not DATETIME since you don't care about the time at all. And you make a very common mistake about the maximum value for time - it varies depending on datatype but "23:59:59" is not the upper limit for datetime. It appears you should check the day of the month first to see if it is 1 - so start with DAY(). Then check the month.

I assume that you want to extend this logic into the future which is why I suggest checking month and day. But if you REALLY want to limit this job execution to that small list of dates, then just check as you already do (but with a date variable and without the time checking)

declare @dt datetime; 
set @dt = getdate();
select @dt;

if cast(@dt as date) in ('2020-09-01', '2020-11-01', '2020-12-01', 
          '2021-02-01', '2021-03-01', '2021-05-01', '2021-06-01', '2020-08-07')
begin
   select  'running for ' + convert(char(10), @dt, 102);  
end
else 
begin 
   select 'not running for ' + convert(char(10), @dt, 102);  
end;

A fiddle to use for experimentation. Just assign any value you desire to the variable to see if it works. The variable usage is not required but use to the convenience of testing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.