September 14, 2014 at 7:55 pm
Hi
I need help with finding Dates at interval of x days within a set date range. Example: What is the date at every 17 days from 1/9/2014 to 1/2/2015?
I hope example is clear for someone who can give a function or sql code to archive above. Let me know if any further information is needed.
Thanks
September 14, 2014 at 8:34 pm
One way is to use a numbers or Tally table. Here is a handy function:
http://www.sqlservercentral.com/scripts/tally/100338/
Then you can use that to calculate intervals and add to some date:
DECLARE @StartDate DATE = '1-Jan-2012';
SELECT N, DATEADD(dd,N,@StartDate) AS FutureDate
FROM dbo.itvfTally(1,100)
WHERE N%17=0;
September 14, 2014 at 10:02 pm
Here is a quick example using a Tally/Numbers CTE
😎
USE tempdb;
GO
SET NOCOUNT ON
/* Generate a set of dates at a given interval
from the start date untill the end date
*/
DECLARE @INTERVAL INT = 17;
DECLARE @START_DATE DATE = '2014-09-01'
DECLARE @END_DATE DATE = '2015-09-01'
DECLARE @NUM_DAYS INT = CEILING(DATEDIFF(DAY,@START_DATE,@END_DATE) / (@INTERVAL * 1.0));
;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL)
,(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(@NUM_DAYS + 1) ROW_NUMBER() OVER
(ORDER BY (SELECT NULL)) - 1 AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7)
SELECT
NM.N AS INTERVAL_NUMBER
,DATEADD(DAY,NM.N * @INTERVAL,@START_DATE) AS INTERVAL_DATE
FROM NUMS NM
Top 10 Results
INTERVAL_NUMBER INTERVAL_DATE
---------------- -------------
0 2014-09-01
1 2014-09-18
2 2014-10-05
3 2014-10-22
4 2014-11-08
5 2014-11-25
6 2014-12-12
7 2014-12-29
8 2015-01-15
9 2015-02-01
10 2015-02-18
September 17, 2014 at 8:39 pm
Thanks for the help. 😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply