Generating dates for given date range (SQL Server)

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.

---

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

---

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 (9⁵) 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.

Format Specifiers in Visual Studio Debugger (using C#)

Below are the few format specifiers developers can use while debugging the applications which can help reducing debugging time by certain clicks/expansions (in a Watch window). 
I have tested these code samples using Visual Studio 2017 Community Edition.

1. ac

Often working with LINQ queries, we try to evaluate the expression/query into watch window and on upon stepping through the lines we may need to check the result for the same query and click on small refresh button to get the updated value. That is going to be tedious work if we need to keep evaluating the same for lengthy code. Well, there is a way to avoid that. Using format specifiers (while debugging), you can force the evaluation of the expression.

For example, consider below code, demo class containing ID and the name properties. I have decorated class with DebuggerDisplay attribute to view the elements in watch window. For more details, please visit my article on the same here.

[DebuggerDisplay("{ID}:{Name}")]
public class DemoClass
{
    public int ID { get; set; }
    public string Name { get; set; }
 
}

Inside the main method, I am populating the list of the DemoClass objects.

List<DemoClass> lstDemoClasses = new List<DemoClass>();
for (int i = 0; i < 5; i++)
{
    lstDemoClasses.Add(new DemoClass { ID = i, Name = $"name {i}" });
}
var somequery = lstDemoClasses.Where(obj => obj.ID % 3 == 0).ToList();
 
lstDemoClasses.Add(new DemoClass { ID = 100, Name = "name 100" });
Console.WriteLine();

Consider that you have some query (dummy query) where you want to list the objects whose ID is divisible by 3. And at the same time, you want to list the objects whose ID is even. For the same, I am adding the lambda query in my watch window like this.

Watch Window:

Now the moment I step through the breakpoint above, it is going to add one more object to the collection and query in watch window not going to be evaluated as shown in watch window. In order to evaluate query, one may need to hit that little refresh button and then it would work. For the most cases, it is okay to hit and go back to debugging but with couple of queries together it would be little time consuming.

To avoid these clicks, you can use the format specifiers for the watch window. For example, to force the evaluation of expression in watch window, you can specify ac separated by comma in Name column as below.

So now, when you step through the breakpoint (where it adds an object to collection with id as 100), debugger forces the evaluation of the query in watch window and you don’t need to hit refresh button.

2. results

Another format  specifier comes handy when you need to view the complex queries in watch window that holds list/collection.

For example, below is the sample code for the same. I have list of strings and I am creating dynamic object that holds the list of names with their length (where length of the name is greater than 4).

List<string> names = new List<string>()
{
    "str 1",
    "string 2",
    "test string 3",
    "test string 4121"
};
var linqQuery = names.Where(nm => nm.Length > 4).Select(obj => new
{
    id = obj.Length,
    names = obj
});

Without any specifier, watch window looks something like below:

By default, it shows some of the private variables from this dynamic object and you can change this behavior if you are focused on the results view only. Using results specifier, you would only see the result/collection directly. You can skip the in between privates and avoid one click while debugging.

3. nq

Using this specifier, you can view the string without quotes (no quotes). It is kind of least useful but I thought to mention it here, just in case.

There are few more specifiers you can go through the same here.

Please feel free to comment the suggestion(s) to make it better.