How to Perform While loop with Date in SQL Server?

Question

I want to insert records by date through loop. This is what I tried.

DECLARE @str VARCHAR(500) = '1400,2001,2400,1201,1001,1302'

DECLARE @startDate datetime = '2015-01-01 00:00:00'
DECLARE @endDate datetime = '2015-01-01 23:59:59'

WHILE (@startDate<='2015-01-31 00:00:00')
BEGIN

    WHILE LEN(@str) > 0
    BEGIN
        DECLARE @storeid VARCHAR(100)
        IF CHARINDEX(',',@str) > 0
            SET  @storeid = SUBSTRING(@str,0,CHARINDEX(',',@str))
        ELSE
            BEGIN
            SET  @storeid = @str
            SET @str = ''
            END
            Print @storeid
            Print @startDate

        SET @str = REPLACE(@str,@storeid + ',' , '')
        END
     SET @startDate = @startDate+1;
 END

OUTPUT

 1400
 Jan  1 2015 12:00AM
 2001
 Jan  1 2015 12:00AM
 2400
 Jan  1 2015 12:00AM
 1201
 Jan  1 2015 12:00AM
 1001
 Jan  1 2015 12:00AM
 1302
 Jan  1 2015 12:00AM

But it's not working. Simply it inserts only one time. It doesn't continue. Thanks

@ZoharPeled I didn't mentioned here where to insert. But look at my result. Why the Date loop is not executing after first time? — mohamed faisal
– mohamed faisal, Commented Jan 18, 2017 at 18:34

Siyual · Accepted Answer · 2017-01-18 13:58:05Z

1

EDIT: The question was drastically changed in the grace period. This is an answer to the original question.

You don't need a loop at all (in fact, you really should almost never need loops in SQL).

You can do this by generating the date list with a tally table, and doing a JOIN to it on your INSERT:

Declare @FromDate   Date = '2015-01-01',
        @ToDate     Date = '2015-01-31'


;With   E1(N) As (Select 1 From (Values (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) DT(N)),
        E2(N) As (Select 1 From E1 A Cross Join E1 B),
        E4(N) As (Select 1 From E2 A Cross Join E2 B),
        E6(N) As (Select 1 From E4 A Cross Join E2 B),
        Tally(N) As
        (
            Select  Row_Number() Over (Order By (Select Null)) 
            From    E6
        ),
        Dates As 
        (
            Select  DateAdd(Day, N - 1, @FromDate) Date
            From    Tally
            Where   N <= DateDiff(Day, @FromDate, @ToDate) + 1
        )
INSERT INTO Transaction_Info
    SELECT 
        TransactionNumber,StoreID,Time
    FROM 
        [HQMatajer].[dbo].[Transaction] T
    JOIN
        Dates   D ON D.Date = Convert(Date, T.Time)
    WHERE 
        StoreID=1302;

answered Jan 18, 2017 at 13:58

Siyual

17k8 gold badges47 silver badges60 bronze badges

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

4 Comments

GuidoG Over a year ago

just curious, I dont see any edits in the question so what do you mean by the question was drastically changed ?

Siyual Over a year ago

Edits made in the grace period (the first 5 minutes, I think?) by the author do not appear in the Edit history.

GuidoG Over a year ago

Oh I did not knew that

mohamed faisal Over a year ago

@Siyual Why my date loop is not executing after first time?

Collectives™ on Stack Overflow

How to Perform While loop with Date in SQL Server?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related