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-2018 and 26-07-2018 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
-- 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
for instance: need list of week numbers between 05-07-2018 and 26-07-2018 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
-- 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
Post a Comment