Suppose My Date is = 2023-03-28 I want to create quarters as shown in below table.

I Want to take care of leap year as well, that's why I used example with leap year that is for ex. Q4-end date is 29.

My date can be random. How can I do this in SQL using a function, a stored procedure, or anything like that?

CodePudding user response：

Find the Q1 start date by subtracting the day of date and adding 1 day. Rest all quarter start date can be determined by just adding 1 day to the previous quarter end date. End date of all quarters can be determined by adding 3 months to the start date of the specific quarter and adding 1 day.

Query

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;

CodePudding user response：

You may use EOMONTH and DATEADD functions as the following:

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]

See demo