Browse DevX
Sign up for e-mail newsletters from DevX


Build Date Generators and Manipulate Date and Time Data in SQL

Learn how to build date generators without loops using SQL, and some useful techniques to help you manipulate date and time data.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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

You can use SQL to generate sequences of numbers in many ways (I personally know about 10). The above listing keeps things uncomplicated by using a simple loop. The amount of numbers (365) in the auxiliary table is equal to the number of days in the year 2007 and they have been calculated automatically using the SQL Server DATEDIFF function. Now, you can generate the sequence of days in year 2007 as follows:

DECLARE @iniDate smalldatetime; SELECT @iniDate = 'Dec 31, 2006'; SELECT DATEADD(dd, num, @iniDate) DayOfYear FROM sequence;


DayOfYear ----------------------- 2007-01-01 00:00:00 2007-01-02 00:00:00 2007-01-03 00:00:00 2007-01-04 00:00:00 . . . . . . . . . . 2007-12-27 00:00:00 2007-12-28 00:00:00 2007-12-29 00:00:00 2007-12-30 00:00:00 2007-12-31 00:00:00

Generating the sequence of days for one year is very simple and fast. However, the task may become more complicated if days of the year don't give you enough granularity for your analyses and you need to dig deeper. You may want to generate the hours, minutes, or even seconds of the year, for example. In the last case, you would need to produce 365 * 24 * 60 * 60 = 31,536,000 date/time values.

Of course, you have a design option for your data warehouse. Instead of creating a star schema, where each dimension table will be directly connected to the fact table, you can employ a snowflake schema, where dimensions can have more complicated structures and consist of a few joined tables. An upcoming section talks about needing to generate a large amount of date/time values. From a technical perspective, you can do that using the idea shown in the previous code, but with a couple of changes. You need to load 31,536,000 numbers into an auxiliary table and then replace the days with seconds in the DATEADD function as follows:

DECLARE @startDate datetime, @iniDate datetime; SELECT @iniDate = 'Dec 31, 2006 23:59:59'; SELECT DATEADD(ss, num, @iniDate) secOfYear FROM sequence order by 1;


secOfYear ----------------------- 2007-01-01 00:00:00.000 2007-01-01 00:00:01.000 2007-01-01 00:00:02.000 2007-01-01 00:00:03.000 . . . . . . . . . . 2007-12-31 23:59:56.000 2007-12-31 23:59:57.000 2007-12-31 23:59:58.000 2007-12-31 23:59:59.000

Such a solution has a few drawbacks. Loading 31,536,000 numbers into a table—even without the loop—will take time. When loaded, that table will occupy about 0.5GB. Also, if you use the auxiliary table for many applications, tasks, and users, your single table will slow down the queries that don't need all the numbers and will use a WHERE clause to restrict the amount of returned data. To make it faster, you need to create a clustered index and maintain it. You can avoid all these drawbacks by slightly changing the approach as follows:

-- Hours of the year --------------------------------------------- DECLARE @iniDate smalldatetime; SELECT @iniDate = 'Dec 31, 2006'; SELECT DATEADD(hh, t2.num - 1, t1.d) YearHour FROM (SELECT DATEADD(dd, num, @iniDate) d FROM sequence) t1 CROSS JOIN (SELECT num FROM sequence WHERE num < 25) t2 ORDER BY 1;


YearHour ----------------------- 2007-01-01 00:00:00 2007-01-01 01:00:00 2007-01-01 02:00:00 ................... 2007-12-31 20:00:00 2007-12-31 21:00:00 2007-12-31 22:00:00 2007-12-31 23:00:00 (8760 row(s) affected) -- Minutes of year DECLARE @iniDate smalldatetime; SELECT @iniDate = 'Dec 31, 2006'; SELECT DATEADD(mi, t4.num - 1, t3.h) FROM (SELECT DATEADD(hh, t2.num - 1, t1.d) h FROM (SELECT DATEADD(dd, num, @iniDate) d FROM sequence) t1 CROSS JOIN (SELECT num FROM sequence WHERE num < 25) t2) t3 CROSS JOIN (SELECT num FROM sequence WHERE num < 61) t4 ORDER BY 1


YearMin ----------------------- 2007-01-01 00:00:00 2007-01-01 00:01:00 2007-01-01 00:02:00 ......................... 2007-12-31 23:56:00 2007-12-31 23:57:00 2007-12-31 23:58:00 2007-12-31 23:59:00 (525600 row(s) affected)

Similarly, you can generate the seconds of the year. The solution in the above code is slower than solutions from the previous two. However, it gives you another alternative for producing sequences of dates in SQL.

Thanks for your registration, follow us on our social networks to keep up-to-date