SQLUSA
BI TRIO 2008
FREE TRIAL
CLICK HERE TO ORDER

SQL Server 2008 Best Practices
SQL Server 2005 Best Practices

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 

*/

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

 

The World Leader in SQL Server 2008 Training
The future is just a CLICK away. Your future!
 
SQLUSA.com Home Page

Copyright 2005-2010, SMI Corp. All Rights Reserved.

SQL Server 2008 is a program product of Microsoft Corporation.
SQL Server 2005 is a program product of Microsoft Corporation.
SQL Server 2000 is a program product of Microsoft Corporation.