CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
user9365324
user9365324

Reputation:

CTE Recursive by Minutes

I'm trying to make a CTE Recursive code that tells me both the date and time but changes by the minute.

DECLARE @MinDate date = '02/10/18'
       ,@EndDate date = DATEADD(MINUTE, n, '00:00:00')

WITH MinuteData As
(
    SELECT @MinDate AS TimeStamp
    UNION ALL
    SELECT DATEADD(MINUTE, 1, TimeStamp)
    From MinuteData
    Where TimeStamp < @EndDate
)

Upvotes: 0

Views: 757

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

You did not state your RDBMS. This code is for SQL-Server which you might have to adapt for another product (for the next time: Always state your RDBMS with the actual version as question tag!)

If I get this correctly, you just want to get a list of DATETIME values, each increased by one minute between a given start and a given end.

Try something like this

DECLARE @StartDate DATETIME={d'2018-10-02'};
DECLARE @EndDate DATETIME={ts'2018-10-02 12:30:00'};

WITH Tally AS
( SELECT TOP(60*24) ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 AS Nmbr 
  FROM master..spt_values)
SELECT DATEADD(MINUTE,Nmbr,@StartDate) AS YourChangingDateTime
FROM Tally
WHERE DATEADD(MINUTE,Nmbr,@StartDate)<=@EndDate;

The cte "Tally" will create a numbers table on the fly. You might also use a physical numbers table - very handsome for many issues!

TOP (60*24) is the count of minutes of 1 day, you can change this, if you need more.

master..spt_values is just a prefilled table with quite a lot of values. It's the easiest way to simulate a list with many rows.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

How about using datetimes?

DECLARE @MinDateTime datetime = '2018-02-10',
        @EndDateTime datetime = DATEADD(MINUTE, n, @MinDateTime);

WITH MinuteData As (
      SELECT @MinDateTime AS TimeStamp
      UNION ALL
      SELECT DATEADD(MINUTE, 1, TimeStamp)
      From MinuteData
      Where TimeStamp < @EndDateTime
    )

Upvotes: 0

Related Questions