October 6, 2015 at 8:51 am
Can you please help to make it workable code..
declare @i int=1
declare @numweek int=2
declare @a int=35
declare @b-2 int=29
declare @Wkstr1 date,@Wkstr2 date
declare @Wkend1 date,@Wkend2 date
while (@i<@numWeek)
begin
set @Wkstr+@i=dateadd(Wk, datediff(Wk, 6, getdate()), -@a)
set @Wkend+@i=dateadd(Wk, datediff(Wk, 6, getdate()), -@b)
set @i=@i+1
set @a=@a+7
set @b-2=@b+7
end
select @Wkstr1
select @Wkstr2
select @Wkend1
select @Wkend2
October 6, 2015 at 9:08 am
What's wrong with it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2015 at 9:12 am
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '+'.
October 6, 2015 at 9:20 am
This is the part that's failing...
set @Wkstr+@i=dateadd(Wk, datediff(Wk, 6, getdate()), -@a)
set @Wkend+@i=dateadd(Wk, datediff(Wk, 6, getdate()), -@b)
You can't set the sum of two variables like that...
It's not really clear what you're trying to accomplish so it's tough to tell you the correct fix.
October 6, 2015 at 9:29 am
I want to set start of week and end of week date in different variable like below..
If i want to do for 12 weeks, for that i need to write 12 statements like below so thought to put in loop
and thought to assign?
declare @Wkstr1 date, @Wkstr2 date,@Wkstr3 date,@Wkstr4 date,@Wkstr5 date,@Wkstr6 date,@Wkstr7 date,@Wkstr8 date
declare @Wkend1 date,@Wkend2 date,@Wkend3 date,@Wkend4 date,@Wkend5 date,@Wkend6 date,@Wkend7 date,@Wkend8 date
set @Wkstr1=dateadd(Wk, datediff(Wk, 6, getdate()), 0)
set @Wkend1= dateadd(Wk, datediff(Wk, 6, getdate()), 6)
select @Wkstr1
select @Wkend1
set @Wkstr2=dateadd(Wk, datediff(Wk, 6, getdate()), -7)
set @Wkend2= dateadd(Wk, datediff(Wk, 6, getdate()), -1)
select @Wkstr2
select @Wkend2
set @Wkstr3=dateadd(Wk, datediff(Wk, 6, getdate()), -14)
set @Wkend3= dateadd(Wk, datediff(Wk, 6, getdate()), -8)
select @Wkstr3
select @Wkend3
set @Wkstr4=dateadd(Wk, datediff(Wk, 6, getdate()), -21)
set @Wkend4= dateadd(Wk, datediff(Wk, 6, getdate()), -15)
select @Wkstr4
select @Wkend4
set @Wkstr5=dateadd(Wk, datediff(Wk, 6, getdate()), -28)
set @Wkend5= dateadd(Wk, datediff(Wk, 6, getdate()), -22)
select @Wkstr5
select @Wkend5
October 6, 2015 at 10:23 am
You can't put that into a loop. Do it the way you wrote in the second post, each variable explicitly assigned one by one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2015 at 10:31 am
SQL Server works better when you try to handle things like this with tables.
perhaps something like this would work better for you:
DECLARE @Weeks TABLE (
Week INT IDENTITY,
StartDate DATE,
EndDate DATE
)
DECLARE @NumWeeks SMALLINT = 5
DECLARE @i SMALLINT = 0
WHILE @i < @NumWeeks
BEGIN
INSERT INTO @Weeks
SELECT
StartDate = dateadd(Wk, datediff(Wk, 6, getdate()), (@i * -7)),
EndDate = dateadd(Wk, datediff(Wk, 6, getdate()), (@i * -7 )+ 6)
SET @i = @@IDENTITY
END
select * from @Weeks
October 6, 2015 at 10:35 am
Many Thanks Mike.. it would be useful.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply