|
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
*/
------------
|
