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.
Introduction
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.
Tally Tables
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.
set nocount on;
if object_id('dbo.Tally') is not null drop table dbo.tally
go
select top 10000 identity(int,1,1) as ID
into dbo.Tally from master.dbo.SysColumns
alter table dbo.Tally
add constraint PK_ID primary key clustered(ID)
go
select * 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.
--Generate Date Range
declare @StartDate datetime
declare @EndDate datetime
set @StartDate=getdate()
set @EndDate=getdate()+25
select dateadd(DD,ID-1,@StartDate) as [DATE],
day(dateadd(DD,ID-1,@StartDate)) as [DAY],
month(dateadd(DD,ID-1,@StartDate)) as [MONTH],
year(dateadd(DD,ID-1,@StartDate)) as [YEAR]
from dbo.Tally
where dateadd(DD,ID-1,@StartDate)<=@EndDate
Output: