NumberOfWeek

How to find number of week in any year when we know the date.

Create Function :

CREATE FUNCTION dbo.GetNoWeek (@startDate datetime)
RETURNS varchar(10)
BEGIN
DECLARE @answer int
DECLARE @theMonth int
DECLARE @theYear int
DECLARE @firstDate as varchar(10)
SET @theMonth = Month(@StartDate)
SET @theYear = Year(@StartDate)
SET @firstDate = CAST( @theYear as varchar(4))+'-'+CAST(@theMonth as varchar(2))+'-'+'01'
SET @answer =dbo.NoOfDayWeek (CAST(@firstDate as datetime),@startDate,'w')
RETURN (@answer)
END

Test :

select dbo.GetNoWeek('2007-01-31')


Result :