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.


Learn more tricks

Add Comment

Comments

30th August 2021

http://buysildenshop.com/ - Viagra

Waisuts

8th September 2021

Blastofen 20mg

buy cialis online with prescription

12th September 2021

cialis 5mg online

AwasmaB

12th September 2021

Acheter Du Propecia France

stromectol amazon