1

How can I run a stored procedure only the last 3 days of a month using a T-SQL script?

  • You can schedule a Job for this in SQL Server Agent. – Rajesh Ranjan Jul 17 at 3:44
  • 1
    SQL agent job only schedule either first day of the month or last day of the month. But , I want to run store procedure last 3 days of a moth. – Phanindra Pothuri Jul 17 at 3:55
1

Adding this script will help you to execute the SP only on the last three days of month.

Note: In the scheduler you make a schedule to execute it daily.

DECLARE @FromDate DATE
DECLARE @ToDate DATE
SELECT @FromDate=DATEADD(DD,-2,CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) AS DATE)) --Last Date -2
SELECT @ToDate= CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) AS DATE) --Last Date of current month

IF (GETDATE() BETWEEN @FromDate AND @ToDate)
BEGIN

EXEC SP_YourSPName--------------------------Execute your SP here

END

I took query to get the last date of the month from https://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0))

Same can be done using the query @Chris Rolliston mentioned in his comment.

DECLARE @Today DATE = GETDATE();
DECLARE @EOM DATE = EOMONTH(@Today);
IF (DAY(@EOM) - DAY(@Today) BETWEEN 0 AND 2)
EXEC SP_YourSPName;

Thanks!

  • Thank you so much! – Phanindra Pothuri Jul 17 at 4:49
  • 1
    Those expressions hurt my eyes! mssqltips.com/sqlservertip/6088/… – Aaron Bertrand Jul 17 at 5:00
  • 1
    @RajeshRanjan Referencing a 12 year old source is a terrible way to prove a point... – Mark Sinkinson Jul 17 at 7:01
  • @RajeshRanjan - he's saying, the syntax you chose for the date calculation isn't very clear, for someone not ultra-familiar with traditional T-SQL data fiddling. For example, take the use of the 'mm' and 'm' shortcuts - unless someone is ultra-familiar, the natural questions are 'what do they mean, and are they different?'. Your actual answer to the question (schedule a daily job, and do the date check in it), is fine. – Chris Rolliston Jul 17 at 7:09
  • 1
    @RajeshRanjan - how about this: DECLARE @Today DATE = GETDATE(); DECLARE @EOM DATE = EOMONTH(@Today); IF DAY(@EOM) - DAY(@Today) BETWEEN 0 AND 2 EXEC SP_YourSPName; – Chris Rolliston Jul 17 at 7:11

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.