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