Build Date Generators and Manipulate Date and Time Data in SQL

ometimes you need to generate sequences of date and/or time values. For example, take a data warehouse that has a multidimensional structure and stores the measures and dimensions in fact and dimension tables. Since one of the dimensions is almost always the time dimension, you would need to generate the sequence of date (time) values and load them into the time dimension table. This article demonstrates how to build date generators without the loops using SQL. In addition, it will show some useful techniques that can help you manipulate date and time data.

Date Generators
First, let's create and load an auxiliary table that consists of one integer column:

SET NOCOUNT ON;
DECLARE	@startDate smalldatetime, @endDate smalldatetime, @i int;
SELECT @i = 1,
@startDate = 'Jan 01, 2007',
@endDate = DATEADD(yy, 1, @startDate);
IF EXISTS(SELECT * FROM sysobjects
WHERE ID = (OBJECT_ID('dbo.sequence')) AND xtype = 'U')
DROP TABLE dbo.sequence;
CREATE TABLE dbo.sequence(num int NOT NULL);
WHILE(@i <= (SELECT DATEDIFF(dd, @startDate, @endDate)))
BEGIN
INSERT INTO dbo.sequence VALUES(@i);
SET @i = @i + 1;
END