April 3, 2018

Sreekanth B

How to Get List of week numbers between the given date range In Sql Server

How to get the list of week numbers between two dates with Monday as the week start day.

for instance: need list of week numbers between 05-07-2017 and 26-07-2017 with Monday as the week start day.


Writing the stored procedure as given below:

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
How to Get List of week numbers between the given date range In Sql Server-- 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

END

Subscribe to get more Posts :