创建自定义季度，从给定日期开始和结束日期

Question

假设My Date = 2023-03-28我想创建如下表所示的季度。

tbody> <<tr>

Q1-StartDate	Q2-StartDate	Q2-StartDate	Q3-StartDate	Q3-EndDate	Q4-StartDate	Q4-StartDate	Q4-EndDate	Q4-StartDate	Q4-EndDate
2023-03-01	2023-05-31	2023-06-01	2023-08-31	2023-09-01	2023-11-30	2023-12-01	2024-02-29

通过减去日期的第几天再加上1天来找到Q1的开始日期。其余所有季度开始日期可以通过在上一个季度结束日期上添加1天来确定。所有季度的结束日期可以通过在特定季度的开始日期上加3个月再加1天来确定。

查询

declare @dt as date;
set @dt = '2023-03-28';
declare @q1_start as date, 
@q1_end as date,
@q2_start as date,
@q2_end as date,
@q3_start as date,
@q3_end as date,
@q4_start as date,
@q4_end as date;
set @q1_start = dateadd(day, -day(@dt) + 1, @dt);
set @q1_end = dateadd(day, -1, dateadd(month, 3, @q1_start));
set @q2_start = dateadd(day, 1, @q1_end);
set @q2_end = dateadd(day, -1, dateadd(month, 3, @q2_start));
set @q3_start = dateadd(day, 1, @q2_end);
set @q3_end = dateadd(day, -1, dateadd(month, 3, @q3_start));
set @q4_start = dateadd(day, 1, @q3_end);
set @q4_end = dateadd(day, -1, dateadd(month, 3, @q4_start));
select @q1_start as Q1_Start,
@q1_end as Q1_End,
@q2_start as Q2_Start, 
@q2_end as Q2_End, 
@q3_start as Q3_Start, 
@q3_end as Q3_End, 
@q4_start as Q4_Start, 
@q4_end as Q4_End;

可以这样使用EOMONTH和DATEADD函数:

DECLARE @sdate Date;
SET @sdate = '2023-03-28';
SELECT 
DATEADD(DAY,1,EOMONTH(@sdate,-1)) [Q1-StartDate], EOMONTH(@sdate,2)  [Q1-EndDate],
DATEADD(DAY,1,EOMONTH(@sdate,2))  [Q2-StartDate], EOMONTH(@sdate,5)  [Q2-EndDate],
DATEADD(DAY,1,EOMONTH(@sdate,5))  [Q3-StartDate], EOMONTH(@sdate,8)  [Q3-EndDate], 
DATEADD(DAY,1,EOMONTH(@sdate,8))  [Q4-StartDate], EOMONTH(@sdate,11) [Q4-EndDate]

看到演示