Get List of week numbers between the given date range using SQL

Select list of week in sql:

The below query will help you to get list of week number between given data range. In the temp table I have saved the week number between start date and end date which user gave as a input.For the Looping I have used WHILE loop to get the weekno from the date range.

DECLARE @startDate DATE = '2013-07-01', @endDate DATE = '2013-07-31';

 -- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @Counter INT

DECLARE @NextDate DATE

DECLARE @WeekNo INT

DECLARE @MyTable TABLE

(

WeekNo int

)

SET @Counter = 0

SET @NextDate = DATEADD(dd,@Counter + 1,@StartDate)

WHILE @NextDate < @EndDate

BEGIN

IF @NextDate > @StartDate

BEGIN

SET @Counter = @Counter + 1

END

SET @NextDate = DATEADD(dd,@Counter,@StartDate)

SET @WeekNo = DATEPART(wk, @NextDate)

IF ((select count(1) from @MyTable where WeekNo = @WeekNo) = 0)

BEGIN

insert into @MyTable values (@WeekNo)

END

END

SELECT * FROM @Mytable

The Output:

WeekNo

27

28

29

30

31