SQL 函数用于在上半月(1 到 15)和下半月(16 到 30/31)之间拆分日期

Question

>假设我正在传递 startDat='2020-03-31 00：00：00.000' 结束日期：'2020-04-21 00：00：00.000' 所以预期输出是;

from_date                          to_date
-----------                       ------------
2020-03-31                         2020-03-31
2020-04-01                         2020-04-15
2020-04-16                         2020-04-21

Answer 1

DECLARE @Date1 DATE = '03-31-2020'
DECLARE @Date2 DATE = '04-21-2020'
DECLARE @Day INT = 0
DECLARE @Month INT = 0
DECLARE @Year INT = 0
DECLARE @MidDayofMonth INT = 15
DECLARE @EOM INT 
DECLARE @table TABLE (StartDate DATE, EndDate DATE)
WHILE @Date1 <= @Date2
BEGIN
SET @Day =  DATEPART(DAY,@Date1)
SET @Month = DATEPART(MONTH,@Date1)
SET @Year = DATEPART(YEAR,@Date1)
IF @Day < 15
BEGIN
INSERT INTO @table
SELECT CASE WHEN  @Day < 15
THEN @Date1
END AS StartDate,
CASE WHEN @Date1 < @date2 AND  @Date2 > CONCAT(@Month,'-',@MidDayofMonth,'-',@Year)  
THEN CONCAT(@Month,'-',@MidDayofMonth,'-',@Year) 
ELSE @Date2 END
AS EndDate
SET @Date1 = DATEADD(DAY,1,CONCAT(@Month,'-',@MidDayofMonth,'-',@Year))
END
ELSE
BEGIN
INSERT INTO @table
SELECT @Date1 AS StartDate,
CASE WHEN @Date1 <= @date2 AND  @Date2 > EOMONTH(@Date1) 
THEN EOMONTH(@Date1) 
ELSE @Date2 END
AS EndDate
SET @Date1 = DATEADD(DAY,1,EOMONTH(@date1))
END
END
SELECT * FROM @table

结果