Get the last day of the year based on a date range

Question

I am trying to get the last day of the year from a date range that was given as parameters to be able to go through the number of years and in each iteration, determine if the start date and end date change to be able to assign a new date,

I have 2 variables, @startDate and @endDate, these are the general range that my cursor will go through, and I have 2 other variables, which are the parameters that are given to the date range of the query that I use to find data to the table , is it possible to determine the number of years to be able to iterate them and in each turn set the new dates?

startDate = '01-APR-15'
endDate = '30-APR-2021'

//Goal
StartDate |EndDate
01-APR-15 |31-DEC-15
01-JAN-16 |31-DEC-16
01-JAN-17 |31-DEC-17
01-JAN-18 |31-DEC-18
01-JAN-19 |31-DEC-19
01-JAN-20 |31-DEC-20
01-JAN-21 |30-APR-21


//something like that
while(startDate < endDate)
   set @newEndDate = '31-DEC-15';
   set @newStartDate = '01-JAN-16';
   .
   .
   select * from where DATEFIELD between @newStartDate and @newEndDate

I attach my code, Im a little confused about it

DECLARE @StartDate datetime = '01-JAN-2015', 
@endDate datetime = '30-APR-2021',  @acumVarchar2 nvarchar(max) = '',
@length2 int, @COUNT2 INT = 0,
@myquery nvarchar(max),
@newStartDate datetime,
@newEndDate datetime,
@DATE22 nvarchar(4);

DECLARE insert_data CURSOR FOR WITH CTE as 
(
select  datepart(year, @StartDate) as yr
    union all
    select  yr + 1 from CTE where yr < datepart(year, @endDate)
)
select yr from CTE
OPEN insert_data;
;with  CTE as
(
select  datepart(year, @StartDate) as yr
    union all
    select  yr + 1 from CTE where yr < datepart(year, @endDate)
)

select  @length2= COUNT(*) from CTE 

FETCH NEXT FROM insert_data INTO @DATE22;
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@COUNT2 < @length2-1)
    BEGIN
        set @acumVarchar2 = @acumVarchar2 + (@DATE22 + ', ')
        SET @COUNT2 = @COUNT2 +1
    END
    ELSE IF(@COUNT2 = @length2-1)
    BEGIN
        set @acumVarchar2 = @acumVarchar2 + (@DATE22)
        set @myquery = @acumVarchar2;
    END

       print @myquery;

       -- here should be the validation of the new date

       insert into TABLEWHERESAVEINFO(Key, Value)
       select Key, Value
      from TABLE1 ii left outer join 
      (
          select  * from pedimp
          where DATEFIELD between @newStartDate and @newEndDate
       ) TABLE2 on table1.ID = table2.ID


FETCH NEXT FROM insert_data INTO  @DATE22;
END;
CLOSE insert_data;
DEALLOCATE insert_data;



-- here is the print
-- 2015, 2016, 2017, 2018, 2019, 2020, 2021

Answer 1

Hi @Victor Cordova ,

Welcome to Microsoft Q&A!

Please refer below and check whether it is helpful to you.

DECLARE @StartDate varchar(20)= '01-APR-2015',   
@endDate varchar(20)='30-APR-2021'   
    
DECLARE insert_data  CURSOR FOR     
with CTE AS  
(  
    Select cast(@StartDate as date) As StartDate,  
    Case when datediff(year,cast(@StartDate as date) ,cast(@EndDate as date))>1 then   
    cast(DATEADD(yy, DATEDIFF(yy, 0, cast(@StartDate as date)) + 1, -1) as date) end EndDate  
    UNION ALL  
    Select dateadd(day,1,EndDate) As StartDate,  
    case when datediff(year,EndDate,cast(@EndDate as date) )>1 then   
    cast(DATEADD(yy, DATEDIFF(yy, 0, dateadd(day,1,EndDate)) + 1, -1) as date)  
    else cast(@endDate as date)  end As EndDate  
    From CTE  
    where datediff(year,EndDate,cast(@EndDate as date))<>0  
)  
select UPPER(format(StartDate,'dd-MMM-yy')) STARTDATE,UPPER(format(EndDate,'dd-MMM-yy')) ENDDATE   
from CTE  

OPEN insert_data     
   
DECLARE @newStartDate DATE,@newEndDate DATE   
FETCH NEXT FROM insert_data INTO @newStartDate,@newEndDate  
   
WHILE @@FETCH_STATUS=0    
BEGIN  
  
    insert into TABLEWHERESAVEINFO(Key, Value)  
        select Key, Value  
       from TABLE1 ii left outer join   
       (  
           select  * from pedimp  
           where DATEFIELD between @newStartDate and @newEndDate  
        ) TABLE2 on table1.ID = table2.ID  
   
FETCH NEXT FROM insert_data INTO @newStartDate,@newEndDate  
END  
   
CLOSE insert_data    
DEALLOCATE insert_data  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.