Ever came across situation where you need to find out dates for the given date range. I recently did and came up with below approaches to do the same.
Note: I have used SQL server 2012 to test these code changes.
Assume that you are given a start date as '11-21-2017' and end date as '12-10-2017' and you need to list out (generate) all the dates in between. How would you do that?
You can basically reference any of the tables with number of records which is greater than the date difference of the from date and to date and should be able to run below query.
Output:
Another way to do the same is using while loop. You can start from beginning date and keep looping until you reach to the end date.
For the first approach, you can rely on tables as long as you have the number of rows more than the date difference but what if you need to test out the date range with difference more than the number of record in that table.
With common table expression variables, you can create rows using (Table Value Constructors) and generate n rows without referencing any table. Simple implementation is as below. It also generates the same list as above.
Try playing around the number of records you can generate with cross joins. In my case, it would generate (95) records, which are good enough to find the dates for a huge range. If you need more, you can keep adding the CTE variable and it would work.
If you have other ways to achieve the same, please feel free to leave the comments/suggestions.
Note: I have used SQL server 2012 to test these code changes.
Assume that you are given a start date as '11-21-2017' and end date as '12-10-2017' and you need to list out (generate) all the dates in between. How would you do that?
You can basically reference any of the tables with number of records which is greater than the date difference of the from date and to date and should be able to run below query.
DECLARE @dt1 DATE = '11-21-2017'; DECLARE @dt2 DATE = '12-10-2017'; CREATE TABLE #tempTable(id INT,GeneratedDate DATE) -- relying on sys.columns as I know there would be only 35 dates for the given range INSERT INTO #tempTable SELECT TOP (DATEDIFF(DAY,@dt1,@dt2)-1) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) [c], NULL FROM sys.columns UPDATE #tempTable SET GeneratedDate=DATEADD(DAY,id,@dt1) SELECT id , GeneratedDate FROM #tempTable
Output:
Another way to do the same is using while loop. You can start from beginning date and keep looping until you reach to the end date.
DECLARE @dt1 DATE = '11-21-2017' DECLARE @dt2 DATE = '12-10-2017' DECLARE @counter INT=1; CREATE TABLE #tempTable(GeneratedDate DATE) WHILE (@dt1<@dt2) BEGIN SELECT @dt1 = DATEADD(DAY,@counter,@dt1) INSERT INTO #tempTable ( GeneratedDate ) VALUES ( @dt1 ) END SELECT * FROM #tempTable
With common table expression variables, you can create rows using (Table Value Constructors) and generate n rows without referencing any table. Simple implementation is as below. It also generates the same list as above.
Try playing around the number of records you can generate with cross joins. In my case, it would generate (95) records, which are good enough to find the dates for a huge range. If you need more, you can keep adding the CTE variable and it would work.
DECLARE @dt1 DATE = '11-21-1930'; DECLARE @dt2 DATE = '12-10-2017'; WITH myCTE AS ( SELECT MyTable.number FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)) MyTable(number) ) SELECT TOP (DATEDIFF(DAY,@dt1,@dt2)-1) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) [Index], DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY (SELECT 1)),@dt1) [Generated Date] FROM myCTE cte1,myCTE cte2, myCTE cte3, myCTE cte4, myCTE cte5
If you have other ways to achieve the same, please feel free to leave the comments/suggestions.