How to use a While Loop in SQL to Create a Variable

Question

In need to create an IN clause with a list of dates in it. The list needs to be in descending order. I created a variable named @cols and am trying to populate it with this code:

declare @end date='2016/05/30' 
declare @begin date = DATEADD(month, DATEDIFF(month, 0, @end), 0) ;
declare @curdate date = @end;  -- start on the last day
print @curdate;
print @begin;

DECLARE @cols NVARCHAR (MAX)

while @curdate >=@begin  -- goes from end of the month to beginning of the month
begin
     select @cols = @cols + ',[' + CONVERT(NVARCHAR, @curdate, 106) + ']';
     select @curdate =  DATEADD(DAY,-1,@curdate ) -- subtract a day
end

print @cols;   
print @curdate;
print @begin;

I'm hoping to get 5/30/16, 5/29/16, 5/28/16 etc (properly formatted of course). The code runs without error however @cols is always empty.

You should become familiar with a tally table. You should use in cases like this instead of a loop. sqlservercentral.com/articles/T-SQL/62867 — Sean Lange
– Sean Lange, Commented Aug 3, 2016 at 20:36

Siyual · Accepted Answer · 2016-08-03 20:16:45Z

5

The reason you're getting a NULL @Cols is because it hasn't been assigned a value before entering the while loop. It is still NULL, and NULL + anything = NULL.

To remedy this, you can set @Cols equal to an empty string before the while loop:

DECLARE @cols NVARCHAR (MAX) = ''

answered Aug 3, 2016 at 20:16

Siyual

17k8 gold badges47 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anthony Hancock · Accepted Answer · 2016-08-03 20:51:27Z

0

Why are you passing a list of dates to an IN clause? Can you not just use a simple date range such as below?

DECLARE @enddate = '2016-05-30'

SELECT *
FROM sometable
WHERE somedate >= DATEADD(month, DATEDIFF(month, 0, @enddate), 0)
    AND somedate < EOMONTH(@enddate)

answered Aug 3, 2016 at 20:51

Anthony Hancock

9416 silver badges13 bronze badges

1 Comment

Missy Over a year ago

This is a good idea however I need a list of dates as part of an in clause in a pivot table.

Collectives™ on Stack Overflow

How to use a While Loop in SQL to Create a Variable

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related