As I have From and To date. Something like below,
BeginDate End Date
1989-01-01 00:00:00.000 2015-12-31 00:00:00.000
I need to loop through until i get the list of all the Date's between those 2 (Begin & End Date's) records. I need to know what will be the efficient way of doing this. I have no clue on how to do this. Any help to this will be highly appreciated.
Thanks
This method uses a generated numbers table and is probably faster than looping.
DECLARE @BeginDate DATETIME = '19890101';
DECLARE @EndDate DATETIME = '20151231';
WITH
E1(N) AS ( SELECT 1 FROM ( VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) DT(N)),
E2(N) AS ( SELECT 1 FROM E1 A, E1 B),
E4(N) AS ( SELECT 1 FROM E2 A, E2 B),
Numbers(N) AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL)) - 1 FROM E4
)
SELECT
N,
DATEADD(D, N, @BeginDate) AS TheDate
FROM Numbers
WHERE N <= DATEDIFF(D, @BeginDate, @EndDate)
You can do this with WHILE loop:
DECLARE @sdt DATE = '1989-01-01'
DECLARE @edt DATE = '2015-12-31'
WHILE @sdt <= @edt
BEGIN
PRINT @sdt
SET @sdt = DATEADD(dd, 1, @sdt )
END
Or with recursive CTE:
DECLARE @sdt DATE = '1989-01-01'
DECLARE @edt DATE = '2015-12-31';
WITH cte
AS ( SELECT @sdt AS sdt
UNION ALL
SELECT DATEADD(dd, 1, sdt)
FROM cte
WHERE DATEADD(dd, 1, sdt) <= @edt
)
SELECT *
FROM cte
OPTION ( MAXRECURSION 10000 )
There is also tally table method as in link provided by @Bridge
Actually the answer is tally tables. But if there is not a big interval the difference will be insignificant.
Something like this should work for your purposes:
DECLARE @sd date = '1989-01-01 00:00:00.000'
, @ed date = '2015-12-31 00:00:00.000'
DECLARE @tt TABLE(
[Date] date
)
WHILE(@sd <= @ed) --Loop which checks each iteration if the date has reached the end
BEGIN
INSERT INTO @tt
SELECT @sd AS Date
SET @sd = DATEADD(dd,1,@sd) --This willl increment the date so you actually advance the loop
END
SELECT * FROM @tt
DateAdd(and restrict the range in a similar way). If you have neither of those in your DB, you could generate one on the fly with a CTE.