Generating 365 days in a year from start and end dates

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 384
Most of the time, database developers need to generate dates for report purpose... for example, if you need to display each day-wise total sales even though the items weren't sold on some days... In technical terms, we need to generate report for Sales of each day and display ZERO for missing/holiday dates...

In this type of scenarios, we suppose to generate the sequential dates first and then LEFT join with the data table to display required report...


declare @start DATE = '20150101'
declare @end DATE = '20151231'

;With Date_CTE(dt) AS
(
SELECT @Start
UNION ALL
SELECT DATEADD(dd,1,dt)
FROM Date_CTE
WHERE DATEADD(dd,1,dt)<=@End
)
select dt
from Date_CTE
OPTION (MAXRECURSION 0)


This results the dates starting from 1st Jan, 2015 to 31st Dec ,2015

Comments or Responses

Login to post response