SQL To check for a day of a month

Suppose,  You are asked to design a SSIS package which should run on every 'Friday' of the week except for Friday which falls on 1st day of the month. For example November 1, 2013. This day is Friday and first day of the month.  So in our case this package should not run.


Assumption: You have a table called Calendartable with calendarDate, calendarYearMonth column
Sample data should look like

20140101, 201401
20140102, 201401


Let's first write T-SQL code to find out if it Friday and FirstDay of the month.

declare 
 @RunDate date
, @RunDate_YearMonth varchar(6)
, @ProcessingDateForThisMonth date
, @ProcessPackage int = 0

set @RunDate = '11/01/2013'

select @RunDate_YearMonth = CalendarDate from Calendartable where CalendarDate = @RunDate 

Print @RunDate_YearMonth;


select @ProcessingDateForThisMonth = min(CalendarDate) from Calendartable
where CalendarYearMonthCode = @RunDate_YearMonth --same month as run date
and datename(dw, CalendarDate) = 'Friday'  --day is a Friday
and day(CalendarDate) > 1 --not the 1st day of the month

Print @ProcessingDateForThisMonth

if @RunDate = @ProcessingDateForThisMonth
begin
set @ProcessPackage = 1
end
else
begin
set @ProcessPackage = 0
end

print @ProcessPackage

This sql set the value of ProcessPackage to 0 or 1 depending upon the date and datename. It will set to value of 0 only when the day happen to be firstday of the month and its friday otherwise it will be always 0.

Put Execute SQL Task in your package and take the value as your output value.


Declare @Now Date, @dayofmonth int=0, @ProcessPackage int = 0
Set @Now = getdate()
Set @dayofmonth = day(@now)

IF (@dayofmonth = 1 and datename(dw,@now) = 'Friday')
Set @ProcessPackage = 0
ELSE
SET @ProcessPackage = 1
Select @ProcessPackage

At this point, you should be ready to execute rest of your package based on this value