Saturday, August 3, 2013

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

No comments: