The SQL statement obtains each day, week, month and year according to the start and end date

background ： In many statistical reports, you need to enter the starting point , End date to query data according to different time units , And the column name displayed is your dynamic time unit . Your table data is saved by day , Then you may have to do something at this time sql Function or stored procedure .

1. Get each day in the range according to the start and end dates , Return as a 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')

Result display ：

2. Gets the week in the range based on the start and end dates （ Include the start and end dates of the week ）, Return as a 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'

Result display ：

3. Get the month in the range according to the start and end dates , Return as a 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'

Result display ：

4. Get the year in the range according to the start and end dates , Return as a 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'

Result display ：

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