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

SQL Server: Converting Integer to Binary data

Let's first a create table which will be use to demonstrate conversion of integer to binary values. To keep this thing simple, let's keep our table as small as possible.

Create table NumberTable
(NumberID int IDENTITY Primary Key NOT NULL
,Number int
,Number_Name varchar(100)
)

--Let's insert some data in this table
Insert into  NumberTable
 VALUES (1, 'One'), (2, 'Two'), (3, 'Three'), (4, 'Four'), (5, 'Five')

-- Let's see what we have in this table.
Select * from Number;



--Let's convert this number to binary value. To do so, we will use Common Table Expression

With BinaryConversation AS
(Select Number, Number as WorkingNumber, cast('' as varchar(max)) as binary_values from NumberTable
-- Now we will do Union ALL with converted values from Common table Expression
UNION ALL
Select B.Number, B.WorkingNumber/2, cast(B.WorkingNumber%2 as varchar(max))+B.binary_values
FROM BinaryConversation B
Where WorkingNumber > 0) --This condition keep our results set to only converted values)
Select Number, binary_values from BinaryConversation Where WorkingNumber = 0
Order BY Number
;

Thanks to all those bloggers from whom I learn.

Here's a good resource I found on Binary and Decimal.