|
Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor to generate date sequences:
-- SQL Server date sequence - SQL table variable - SQL month sequence
DECLARE @Sequence TABLE(
NUMBER INT
)
DECLARE @i SMALLINT,
@Limit SMALLINT,
@FirstDayOfMonth DATETIME
SET @i = 1;
SET @Limit = 100;
-- T-SQL while loop
WHILE (@i <= @Limit)
BEGIN
INSERT @Sequence
SELECT @i
SET @i = @i + 1
END -- while
SET @FirstDayOfMonth = DATEADD(DD,1 - DAY(getdate()),convert(VARCHAR,getdate(),111))
PRINT @FirstDayOfMonth
SELECT MonthSequence = dateadd(mm,NUMBER,@FirstDayOfMonth)
FROM @Sequence
ORDER BY NUMBER
GO
/* Partial results
MonthSequence
2019-03-01 00:00:00.000
2019-04-01 00:00:00.000
2019-05-01 00:00:00.000
2019-06-01 00:00:00.000
2019-07-01 00:00:00.000
2019-08-01 00:00:00.000
*/
------------
-- Alternate date sequence
------------
USE AdventureWorks
GO
-- SQL identity function sequence
-- Select into temporary table
-- SQL cross join - Cartesian product
SELECT TOP 2000 ID = identity(INT,1,1),
[Date] = dateadd(DAY,-366,convert(CHAR(10),getdate(),112))
INTO #Date
FROM sys.objects o1
CROSS JOIN sys.objects o2
UPDATE #Date
SET [Date] = Dateadd(DAY,ID,[Date])
SELECT *
FROM #Date
/* Partial results
ID Date
1 2018-02-18 00:00:00.000
2 2018-02-19 00:00:00.000
3 2018-02-20 00:00:00.000
4 2018-02-21 00:00:00.000
5 2018-02-22 00:00:00.000
6 2018-02-23 00:00:00.000
7 2018-02-24 00:00:00.000
*/
-- Cleanup
DROP TABLE #Date
|
