SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[f_GetMonthRange]
(
@startTime datetime,
@endTime datetime
)
RETURNS @table TABLE --输出的数据表
(
[DateMonth] varchar(7)
)
AS
BEGIN
WHILE @startTime <= @endTime
BEGIN
INSERT INTO @table
SELECT CONVERT(varchar(7),DATEADD(mm, DATEDIFF(mm, 0, @startTime), 0) , 120) AS DateMonth
SET @startTime = CONVERT(varchar(7), DATEADD(mm, 1, @startTime) , 120) + '-01'
END
RETURN;
END
1.2.测试效果
select * from F_GetMonthRange('2022-01-01 00:00:00','2022-09-01 00:00:00')
2.获取两个时间之间的日期
2.1.编写表值函数
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[f_GetDateRange](@startTime datetime, @endTime datetime)
RETURNS TABLE
AS
RETURN
(
SELECT convert(char(10), DATEADD(dd, number, @startTime),120) AS DateDay
FROM master.dbo.spt_values as spt
WHERE type = 'p' AND number <= DATEDIFF(DAY, @startTime, @endTime)
)
2.2.测试效果
select * from F_GetDateRange('2022-01-01 00:00:00','2022-09-01 00:00:00')