Background: in many statistical reports, you need to enter start and end dates to query data according to different time units, and the displayed column names are your dynamic time units. Your table data is saved by the day, so you may need to do some sql functions or stored procedures to implement it.

1. Get each day in the range according to the start and end dates, and return as the result set (function)

create function [dbo].[fn_GetDateRange](@DATE_START datetime, @DATE_END datetime)
RETURNS TABLE
AS
RETURN
(
SELECT convert(char(10), DATEADD(dd, number, @DATE_START),120) AS DATA_DATE 
FROM    master.dbo.spt_values  as spt
WHERE   type = 'p' AND number <= DATEDIFF(DAY, @DATE_START, @DATE_END)
)

GO

select * from  fn_GetDateRange('2018-07-31','2018-08-10')

Results:

2. Get the week in the range (including the start and end dates of the current week) according to the start and end dates, and return it as the result set (stored procedure)

CREATE PROCEDURE [dbo].[GetWeekRange]
@tmpDate datetime, @DATE_END datetime
AS
BEGIN
	SET DATEFIRST 1
   DECLARE @index INT
   DECLARE @table TABLE
    (
      WeekTimes INT ,
      FirstDay datetime ,
      EndDay datetime
    )
   DECLARE @YearFistWK INT
   SET @YearFistWK= DATEPART(dw, @tmpDate)
   SET @index = ( SELECT   DATEPART(WEEK, @tmpDate))
   IF ( @YearFistWK = 7 )
    BEGIN 
        SET @index = @index + 1
    END
WHILE @tmpDate <= @DATE_END
    BEGIN
        INSERT  INTO @table
                SELECT  @index ,
                        a.FirstDay ,
                        b.EndDay
                FROM    ( SELECT    1 AS ID ,
                                    DATEADD(wk, DATEDIFF(wk, 0, @tmpDate), 0) AS FirstDAy
                        ) a
                        LEFT JOIN ( SELECT  1 AS ID ,
                                            DATEADD(wk,
                                                    DATEDIFF(wk, 0, @tmpDate),
                                                    6) AS EndDay
                                  ) b ON a.ID = b.ID
 
        SET @tmpDate = DATEADD(DAY, 7, @tmpDate)
        SET @index = @index + 1
		END
		SELECT  WeekTimes, CONVERT(varchar(100), FirstDay, 23) FirstDay,CONVERT(varchar(100), EndDay, 23) EndDay
FROM    @table
END

GO

exec GetWeekRange '2018-07-31','2018-08-10'

Results:

3. Get the months in the range according to the start and end dates, and return as the result set (stored procedure)

CREATE PROCEDURE [dbo].[GetMonthRange] 
@tmpDate datetime, @DATE_END datetime
AS
BEGIN
	DECLARE @table TABLE
    (
	  DateMonth datetime
	)
	WHILE @tmpDate <= @DATE_END
	BEGIN
        INSERT  INTO @table
                SELECT  DATEADD(mm, DATEDIFF(mm, 0, @tmpDate), 0) AS DateMonth
                      
        SET @tmpDate = CONVERT(varchar(7), DATEADD(mm, 1, @tmpDate) , 120) + '-01'
		END
		select CONVERT(varchar(7), DateMonth, 23) DateMonth  from @table
END

GO

exec GetMonthRange '2018-07-31','2018-08-10'

Results:

4. Get the year in the range according to the start and end dates, and return as the result set (stored procedure)

CREATE PROCEDURE [dbo].[GetYearRange] 
@tmpDate datetime, @DATE_END datetime
AS
BEGIN
DECLARE @table TABLE
    (
	  DateYear datetime
	)
	WHILE @tmpDate <= @DATE_END
	BEGIN
        INSERT  INTO @table
                SELECT  DATEADD(yy, DATEDIFF(yy, 0, @tmpDate), 0) AS DateYear
                      
        SET @tmpDate = CONVERT(varchar(4), DATEADD(yy, 1, @tmpDate) , 120) + '-01-01'
		END
		select CONVERT(varchar(4), DateYear, 23) DateYear  from @table
END

GO

exec GetYearRange '2017-08-31','2018-08-10'

Results:

 

There should be better ways. I hope you guys will give me some advice