Loop - SQL Server

Question

good afternoon. All right? I’m trying to print a loop of hours where Data10 should show start date + 10 seconds and Data5 should show start date + 5 minutes I tried the down loop without success:

declare @DataInicial DATETIME, @DataFinal DATETIME, @Data10 DATETIME, @Data5 DATETIME;

SET @DataInicial = '2020-06-19 23:35:00.000'
SET @DataFinal   = '2020-06-19 23:56:00.000'

WHILE @DataInicial<= '2020-06-19 23:56:00.000'
BEGIN
    IF @DataInicial > '2020-06-19 23:56:00.000'
        BEGIN
            BREAK
        END

set @Data10 = DATEADD(SECOND,10,@DataInicial)
set @Data5 = DATEADD(MINUTE,5,@DataInicial)

select @Data10, @Data5

END;

The same shows as a result several times:

2020-06-19 23:35:10.000 | 2020-06-19 23:40:00.000 
2020-06-19 23:35:10.000 | 2020-06-19 23:40:00.000 
2020-06-19 23:35:10.000 | 2020-06-19 23:40:00.000 

Could you signal me the error in mounted manure? Abs!

Answer 1

You are assigning seconds/minutes to variables that are not used in your loop.

See, in this code snippet you assign the result of the functions to the variables @Data10 and @Data5 and then I printed them on the screen:

set @Data10 = DATEADD(SECOND,10,@DataInicial)
set @Data5 = DATEADD(MINUTE,5,@DataInicial)

select @Data10, @Data5

But your loop takes into account the variable @DataInicial, is you are not changing at any time.

I suggest adding the following lines below:

set @Data10 = @DataInicial;
set @Data5 = @DataInicial;

Just after declaring the variables below:

SET @DataInicial = '2020-06-19 23:35:00.000'
SET @DataFinal   = '2020-06-19 23:56:00.000'

Thus remaining:

SET @DataInicial = '2020-06-19 23:35:00.000'
SET @DataFinal   = '2020-06-19 23:56:00.000'
set @Data10 = @DataInicial;
set @Data5 = @DataInicial;

Then you loop based on the @Data10 and @Data5 variables.

Answer 2

Bruno, good night. All right? It ended up working as follows:

declare @DataInicial DATETIME, @DataFinal DATETIME, @Data10 DATETIME, @Data5 DATETIME ;

SET @DataInicial = '2020-06-20 00:00:00.000'
SET @DataFinal   = '2020-06-20 08:56:00.000'

WHILE @DataInicial<= '2020-06-20 08:56:00.000'
BEGIN
    IF @DataInicial > '2020-06-20 08:56:00.000'
        BEGIN
            BREAK
        END

set @Data10 = DATEADD(SECOND,10,@DataInicial) 
set @Data5 = DATEADD(MINUTE,5,@DataInicial)

select @Data10, @Data5

set @DataInicial = DATEADD(MINUTE,5,@DataInicial)
set @DataFinal   = DATEADD(MINUTE,5,@DataInicial)

END;

Upshot:

2020-06-20 00:00:10.000 | 2020-06-20 00:05:00.000
2020-06-20 00:05:10.000 | 2020-06-20 00:10:00.000
2020-06-20 00:10:10.000 | 2020-06-20 00:15:00.000
E assim sucessivamente...

Thank you for your attention!