datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to generate a date sequence with ROW_NUMBER?

Execute the following SQL Server Transact-SQL scripts in Management Studio Query Editor for the generation of the next 1000 dates starting tomorrow:

USE AdventureWorks;

 

-- SQL common table expression - CTE - SQL sequence - SQL date sequence

-- SQL row_number - SQL cross join - SQL over order by

SET NOCOUNT  ON;

WITH cteSequence

     AS (SELECT TOP 1000 [SeqNo] = ROW_NUMBER()

                                     OVER(ORDER BY c1.name)

         FROM   sys.columns c1

                CROSS JOIN sys.columns c2)

SELECT [Date] = dateadd(DAY,SeqNo,convert(CHAR(10),getdate(),110))

FROM   cteSequence ORDER BY SeqNo

 

GO

/* Partial results

 

2019-02-17 00:00:00.000

2019-02-18 00:00:00.000

2019-02-19 00:00:00.000

2019-02-20 00:00:00.000

2019-02-21 00:00:00.000

2019-02-22 00:00:00.000

2019-02-23 00:00:00.000

2019-02-24 00:00:00.000

2019-02-25 00:00:00.000

*/

------------

 

------------

-- Alternative solution

------------

USE AdventureWorks;

 

-- SQL date sequence - SQL sequence table

DECLARE  @RunningMax INT,

         @Limit      INT;

 

SET @Limit = 1000;

 

-- Sequence number table

CREATE TABLE #SequenceNumber (

  SeqNo INT);

 

INSERT INTO #SequenceNumber

VALUES     (1);

 

SET @RunningMax = 1;

 

WHILE (@RunningMax <= @Limit)

  BEGIN

    INSERT INTO #SequenceNumber

    SELECT @RunningMax + SeqNo

    FROM   #SequenceNumber;

    SELECT @RunningMax = MAX(SeqNo)

    FROM   #SequenceNumber;

  END -- while

GO

 

SELECT   [Date] = dateadd(dd,SeqNo,convert(VARCHAR,CURRENT_TIMESTAMP,111))

FROM     #SequenceNumber

ORDER BY SeqNo

GO

 

-- Cleanup

DROP TABLE #SequenceNumber

/* Partial results 

 

2019-02-17 00:00:00.000 

2019-02-18 00:00:00.000 

2019-02-19 00:00:00.000 

2019-02-20 00:00:00.000 

2019-02-21 00:00:00.000 

2019-02-22 00:00:00.000 

2019-02-23 00:00:00.000 

2019-02-24 00:00:00.000 

2019-02-25 00:00:00.000 

*/

------------

 

Exam Prep 70-461
Exam 70-461