SQL Server获取连续区间的日期

2022-10-26 18:37:32

目前实现方法有：

通过系统表master..spt_values获取
用WHILE循环获取
游标获取
CTE递归（感谢博友提供）

方法一：通过系统表master..spt_values获取

1、获取连续的日

-- 获取连续区间的日期

DECLARE @StartTime DATE = '2019-03-08', -- 开始时间

        @EndTime   DATE = '2019-03-18' -- 结束时间

SELECT CONVERT(NVARCHAR(10),DATEADD(DAY,number,@StartTime),120) AS DayTime

  FROM master..spt_values

 WHERE type = 'p'

   AND number <= DATEDIFF(DAY,@StartTime,@EndTime)

2、获取连续的月

-- 获取连续区间的月份

DECLARE @StartTime DATE = '2019-03-08', -- 开始时间

        @EndTime   DATE = '2019-12-28' -- 结束时间

SELECT CONVERT(VARCHAR(7),DATEADD(MONTH,number,@StartTime),120) AS MonthTime

  FROM master..spt_values WITH (NOLOCK)

 WHERE type='P'

   AND number <= DATEDIFF(MONTH,@StartTime,@EndTime)

3、获取连续的年

-- 获取连续区间的年份

DECLARE @StartTime DATE = '2019-03-08', -- 开始时间

        @EndTime   DATE = '2020-03-08' -- 结束时间

SELECT CONVERT(VARCHAR(4),DATEADD(YEAR,number,@StartTime),120) AS YearTime

  FROM master..spt_values WITH (NOLOCK)

 WHERE type='P'

   AND number <= DATEDIFF(YEAR,@StartTime,@EndTime)

master..spt_values原理：

通过number来实现年月日的加减，因为number值最大是2047，所以只能连续加2047。

如图：结束时间是'2026-03-28'，但我们降序可以看到，从2019-03-08只能增加连续2047天，如此2024-10-24之后的日期就没有了。

所以如果连续区间超过了2048，则此方法不适用，此时可以用以下方法二来实现

方法二：用WHILE循环获取

1、获取连续的日

-- 连续获取天

DECLARE @StartTime DATE = '2019-03-08', -- 开始时间

        @EndTime   DATE = '2019-03-18' -- 结束时间

-- 创建临时表#DateTime存储日期

CREATE TABLE #DateTime

(

    DayTime DATE

);

-- 循环获取日期插入临时表

WHILE @StartTime <= @EndTime

BEGIN

    INSERT INTO #DateTime (DayTime)

    VALUES (@StartTime);

    SET @StartTime = DATEADD(DAY, 1, @StartTime);

END;

SELECT DayTime FROM #DateTime;

-- 删除临时表

DROP TABLE #DateTime;

2、获取连续的月

-- 连续获取的月

DECLARE @StartTime DATE = '2019-03-08', -- 开始时间

        @EndTime   DATE = '2019-12-28' -- 结束时间

-- 创建临时表#MonthTime存储日期

CREATE TABLE #MonthTime

(

    MonthTime VARCHAR(7)

);

-- 循环获取日期插入临时表

WHILE @StartTime <= @EndTime

BEGIN

    INSERT INTO #MonthTime (MonthTime)

    VALUES (CONVERT(VARCHAR(7),@StartTime));

    SET @StartTime = DATEADD(MONTH, 1, @StartTime);

END;

SELECT MonthTime FROM #MonthTime;

-- 删除临时表

DROP TABLE #MonthTime;

3、获取连续的年

-- 连续获取的月

DECLARE @StartTime DATE = '2019-03-08', -- 开始时间

        @EndTime   DATE = '2025-03-28' -- 结束时间

-- 创建临时表#YearTime存储日期

CREATE TABLE #YearTime

(

    YearTime VARCHAR(4)

);

-- 循环获取日期插入临时表

WHILE @StartTime <= @EndTime

BEGIN

    INSERT INTO #YearTime (YearTime)

    VALUES (CONVERT(VARCHAR(4),@StartTime));

    SET @StartTime = DATEADD(YEAR, 1, @StartTime);

END;

SELECT YearTime FROM #YearTime;

-- 删除临时表

DROP TABLE #YearTime;

方法三：用游标获取，原理和WHILE循环相似，此处不再展示

方法四：评论区博友说的CTE递归

DECLARE @StartTime DATE = '2018-12-08', -- 开始时间

@EndTime DATE = '2019-03-18' -- 结束时间

;

-- 获取连续天

WITH CteDateDay AS

(

SELECT @StartTime DayTime

UNION ALL

SELECT DATEADD(DAY,1,DayTime) DayTime FROM CteDateDay

WHERE DayTime<@EndTime

)

SELECT DayTime FROM CteDateDay

OPTION (MAXRECURSION 0)

;

-- 获取连续月

WITH CteDateMonth AS

(

SELECT CONVERT(VARCHAR(7),@StartTime,120) MonthTime

UNION ALL

SELECT CONVERT(VARCHAR(7),DATEADD(MONTH,1,CAST(MonthTime+'-01' AS DATE)),120) DayTime FROM CteDateMonth

WHERE MonthTime<CONVERT(VARCHAR(7),@EndTime,120)

)

SELECT MonthTime FROM CteDateMonth

OPTION (MAXRECURSION 0)

;

-- 获取连续年

WITH CteDateYear AS

(

SELECT DATEPART(YEAR,@StartTime) YearTime

UNION ALL

SELECT YearTime+1 DayTime FROM CteDateYear

WHERE YearTime<DATEPART(YEAR,@EndTime)

)

SELECT YearTime FROM CteDateYear

OPTION (MAXRECURSION 0)

如果有什么好的方法和建议，欢迎大家来指点，谢谢！

码农公寓

相关文章