Returning Date Range from Function (Day, Month, Quarter, Year)

Some times we need to return date data as per our requirement like monthly ( start and end date of each) data for given date range. this function do the same


following is the code.


CREATE FUNCTION [dbo].[GetDateRange] (
  @StartDate DATETIME,
  @EndDate   DATETIME,
  @type varchar(20)
)
RETURNS @A TABLE (
  stdate datetime,eddate datetime, PartName varchar(30),[Year] int
)
AS
BEGIN
  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, DatePart(yy, DATE) as [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,DatePart(yy, DATE) as [Year]
FROM CTE
Group by 'Week ' + CAST(DatePart(ww, DATE) as varchar(10)), DatePart(yy, DATE)
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, DatePart(yy, DATE) as [Year]
FROM CTE
Group by CAST(DATENAME(month, DATE) as varchar(10)), DatePart(yy, DATE)
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, DatePart(yy, DATE) as [Year]
FROM CTE
Group by 'Quarter ' + CAST(DatePart(QQ, DATE) as varchar(10)), DatePart(yy, DATE)
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, DatePart(yy, DATE) as [Year]
FROM CTE
Group by 'year ' + CAST(DatePart(yy, DATE) as varchar(10)),DatePart(yy, DATE)
order by MIN(DATE) ASC

OPTION (MAXRECURSION 0)
end


  RETURN;
END


When execute following results will return