Generate Sequence of Dates Using Tally Tables In SQL Server
This article explains, what are tally tables and how to create list of date using tally table.
In this article , I am going to explain how to
create date range using Tally Tables. A Tally Table is a single column table
which are used to calculate anything in a looping manner. It improves the
performance of query by reducing time for its execution.
A Tally Table is like a other table with a
single column that holds unique numbers from 1(or 0) to any large number as per
your requirement. In Tally table, large number should be taken according to your
system or application. So try to avoid using very high number.
Generate Sequence of Dates
Firstly create Tally Table.
top 10000 identity(int,1,1)
* from dbo.Tally
Suppose we want to generate a list of dates
from current date to next 25 days. There is no need to use while loop to create
list of dates. We can easily generate list of dates using Tally table.