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.
  • 6064

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

tallyTables-sql.jpg

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:

DateRange-tallyTable.jpg

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.