question

VictorCordova-8103 avatar image
0 Votes"
VictorCordova-8103 asked VictorCordova-8103 edited

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

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
sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered VictorCordova-8103 edited

Hi @VictorCordova-8103,

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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you it was very helpful, thanks a lot

0 Votes 0 ·