Get All Days/Months/Years/Quarters with in Two dates.

Hi All,

Today i am feeling soo great as i finally got what i needed the function that i am going to show now will return all the Dates with in two dates but wait its not this simple, i can have periods defined like difference would be in weeks / Months / Years / Quarters and so on....

What it will return in end is Minimum Date of reporting period Maximum date of reporting period and name of that Period.

CREATE FUNCTION [dbo].[GetReportingPeriods] (
  @StartDate DATETIME,
  @EndDate   DATETIME,
  @type varchar(20)
)
RETURNS @A TABLE (
  stdate datetime,eddate datetime, PartName varchar(30)
)
AS
BEGIN
/******************************************************************************
 * Author:      USMAN SHABBIR 
 * Create Date: 2010-11-20
 * Description: Create a table of dates between @StartDate and @EndDate
 *****************************************************************************/
  if @type = 'daily'
 begin 
  ;with cte as 
  ( select CONVERT(DATETIME,@startDate) AS DATE 
  UNION ALL 
  select Dateadd(dd,1,DATE) as date 
  from cte   
  where date <= @endDate - 1 
  )  
 
  insert into @a
  SELECT DATE, DATE + 1 - 1, 'Day ' + CAST(DatePart(dd, DATE) as varchar(10))  as Week_NO_Of_Year
  FROM CTE
  --Group by 'Day ' + CAST(DatePart(dd, DATE) as varchar(10))
  order by (DATE) ASC
  
  OPTION (MAXRECURSION 0)
 end

 else if @type = 'Weekly'
 begin 
  ;with cte as 
 ( select CONVERT(DATETIME,@startDate) AS DATE 
 UNION ALL 
 select Dateadd(dd,1,DATE) as date 
 from cte   
 where date <= @endDate - 1 
 )  
 
  insert into @a
  SELECT MIN(DATE), MAX(DATE), 'Week ' + CAST(DatePart(ww, DATE) as varchar(10))  as Week_NO_Of_Year
  FROM CTE
  Group by 'Week ' + CAST(DatePart(ww, DATE) as varchar(10))
  order by MIN(DATE) ASC
  
  OPTION (MAXRECURSION 0)
 end
 else if @type = 'Monthly'
  begin 
  
   ;with cte as 
 ( select CONVERT(DATETIME,@startDate) AS DATE 
 UNION ALL 
 select Dateadd(dd,1,DATE) as date 
 from cte   
 where date <= @endDate - 1 
 )  
  insert into @a
  SELECT MIN(DATE), MAX(DATE), CAST(DATENAME(month, DATE) as varchar(10))  as Week_NO_Of_Year
  FROM CTE
  Group by CAST(DATENAME(month, DATE) as varchar(10))
  order by MIN(DATE) ASC
  
  OPTION (MAXRECURSION 0)
 end
 else if @type = 'quarterly'
  begin 
  
 ;with cte as 
 ( select CONVERT(DATETIME,@startDate) AS DATE 
 UNION ALL 
 select Dateadd(dd,1,DATE) as date 
 from cte   
 where date <= @endDate - 1 
 )  
  insert into @a
  SELECT MIN(DATE), MAX(DATE), 'Quarter ' + CAST(DatePart(QQ, DATE) as varchar(10))  as Week_NO_Of_Year
  FROM CTE
  Group by 'Quarter ' + CAST(DatePart(QQ, DATE) as varchar(10))
  order by MIN(DATE) ASC
  
  OPTION (MAXRECURSION 0)
 end
 else if @type = 'yearly'
  begin 
  
 ;with cte as 
 ( select CONVERT(DATETIME,@startDate) AS DATE 
 UNION ALL 
 select Dateadd(dd,1,DATE) as date 
 from cte   
 where date <= @endDate - 1 
 )  
  insert into @a
  SELECT MIN(DATE), MAX(DATE), 'year ' + CAST(DatePart(yy, DATE) as varchar(10))  as Week_NO_Of_Year
  FROM CTE
  Group by 'year ' + CAST(DatePart(yy, DATE) as varchar(10))
  order by MIN(DATE) ASC
  
  OPTION (MAXRECURSION 0)
 end

  RETURN;
END

Hope it will help some one the same way as it did to me :)

Happy coding.