MS SQL Tips & Tricks

How to create table with date range and interval

To get the data range table with an interval you just need to generate as many records as the required entries multiplying them using Union clause. As each record has subsequent number we simply use this number (multiplier by interval) and add to the start date. 

declare @from DATETIME;
declare @to DATETIME;
declare @step Int;

set @from='2009-01-01';
set @to='2009-01-07';
set @step=15;

SELECT   dateadd(s,(10 * 
		( 10 * 
			( 10 * 
				( 10 * A.digit + B.digit ) + 
				C.digit ) + 
			D.digit ) + 
		E.digit)*@step,@from)
FROM     [GetDigits] () AS A, [GetDigits] () AS B, 
         [GetDigits] () AS C, [GetDigits] () AS D,
         [GetDigits] () AS E
WHERE    dateadd(s,(10 * 
		( 10 * 
			( 10 * 
				( 10 * A.digit + B.digit ) + 
				C.digit ) + 
			D.digit ) + 
		E.digit)*@step,@from) BETWEEN @from AND @to

This problem (as a function definition) is also included in Jacek Szarapa's MS SQL Library.

See also


Learn more tricks

Add Comment

Comments