# 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'//GoalStartDate |EndDate01-APR-15 |31-DEC-1501-JAN-16 |31-DEC-1601-JAN-17 |31-DEC-1701-JAN-18 |31-DEC-1801-JAN-19 |31-DEC-1901-JAN-20 |31-DEC-2001-JAN-21 |30-APR-21//something like thatwhile(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 CTEOPEN 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 = 0BEGINIF(@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`

• This topic was modified 1 month, 4 weeks ago by  dqurve.
• This topic was modified 1 month, 4 weeks ago by  dqurve.
• Hello dqurve

Was also a bit confused by the cursory code sample... Hopefully, this will help.

Be aware it is often good to have a permanent calendar table available in your database.  Such tables greatly simplifies date manipulation.  For details on how to set one up see: https://www.sqlservercentral.com/scripts/calendar-table-function

In the sql below, the two CTEs that come first create a very basic calendar table 'on the fly' using recursion, see: https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15

The calendar table is then queried using a simple group by statement.

`declare @startDate date = '01-Apr-2015';declare @endDate date = '30-Apr-2021';with -- recursive CTE to enumerate day offset numbers between supplied datesmyDayNumbers as(select 0 as numunion all select num + 1 from myDayNumberswhere num < datediff(day,@startDate,@endDate)),-- create a mini calendar table-- by converting the numeric offsets to set of datesmyDays as(select dateadd(day,num,@startDate) as dt from myDayNumbers)-- using calendar table, get required 'goal' date pairs using simple 'group by' queryselect min(dt) as StartDate, max(dt) as EndDatefrom myDaysgroup by year(dt) -- if can be more than 32767 days between start and end dates then set MAXRECURSION to 0option (MAXRECURSION 32767);`

• "...is it possible to determine the number of years to be able to iterate them and in each turn set the new dates?"

To determine the number of years "to iterate" (not!) you're looking for the number of times the year "boundary" was crossed between the 2 dates: 2015 to 2016 is boundary cross 1, 2016 to 2017 is boundary cross 2, etc..  According to the Docs the SQL function DATEDIFF:

"This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate."

`DECLARE  @startDate date='01-JAN-2015',   @endDate date='30-APR-2021';select datediff(year, @startDate, @endDate) dt_diff;`
`dt_diff6`

The 'datepart' is year and the year calendar boundary was crossed 6 times between Jan 1 2015 and Apr 30 2021.

How to generate the new rows required?  This SSC article contains a walkthrough example and explanation of a really good way.  The author of the article provides a very handy table valued function (tvf) called dbo.fnTally which can quickly generate as many rows as necessary.  If you're polite the author is even quite responsive to questions.

`CREATE FUNCTION [dbo].[fnTally]/**********************************************************************************************************************    Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally**********************************************************************************************************************/        (@ZeroOrOne BIT, @MaxN BIGINT)RETURNS TABLE WITH SCHEMABINDING AS  RETURN WITH  H2(N) AS ( SELECT 1                FROM (VALUES                     (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)                    )V(N))            --16^2 or 256 rows, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows            SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL            SELECT TOP(@MaxN)                   N = ROW_NUMBER() OVER (ORDER BY N)              FROM H8;`
`select @startDate start_dt, fn.n tally_n,       dateadd(year, fn.n, @startDate) start_dt_plus_yearfrom dbo.fnTally(0, datediff(year, @startDate, @endDate)) fn;`
`start_dttally_nstart_dt_plus_year2015-01-0102015-01-012015-01-0112016-01-012015-01-0122017-01-012015-01-0132018-01-012015-01-0142019-01-012015-01-0152020-01-012015-01-0162021-01-01`

The 'tally_n' column contains the sequence generated by the tally function and the 'start_dt_plus_year' column contains the start date plus tally_n as year(s).  This is nearly complete it just needs CASE logic to decode the calculated dates.  The code needs to refer to the calculated years (6) in multiple places so it was extracted into it's own virtual table and aliased as 'yr'.

`declare  @startDate date='01-JAN-2015',   @endDate date='30-APR-2021';select @startDate start_dt, fn.n tally_n,       case when yr.dt_diff=fn.n             then @endDate             else datefromparts(year(@startDate)+fn.n, 12, 31) end answerfrom (values (datediff(year, @startDate, @endDate))) yr(dt_diff)     cross apply dbo.fnTally(0, yr.dt_diff) fnorder by start_dt;`
`start_dttally_nanswer2015-01-0102015-12-312015-01-0112016-12-312015-01-0122017-12-312015-01-0132018-12-312015-01-0142019-12-312015-01-0152020-12-312015-01-0162021-04-30`

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• It's the same idea with a clock as with a calendar

https://www.sqlservercentral.com/forums/topic/get-total-no-of-minutes-by-hour-hand-between-two-dates#post-3900058

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• Yeah, thanks a lot

• Thank you @bredon

• You don't need to use the fnTally function - it can be done inline.

`Declare @startDate date = '2015-04-15'      , @endDate date = '2021-04-30';   With t(n)     As ( Select t.n   From (Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)                      )      , iTally (YearNumber)                   As ( Select Top (datediff(year, @startDate, @endDate) + 1)        year(@startDate) + checksum(row_number() over(Order By @@spid)) - 1   From t t1, t t2  --100 years                     ) Select StartDate = iif(it.YearNumber = year(@startDate), @startDate, datefromparts(it.YearNumber, 1, 1))      , EndDate = iif(it.YearNumber = year(@endDate), @endDate, datefromparts(it.YearNumber, 12, 31))   From iTally  it;`

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

Viewing 8 posts - 1 through 8 (of 8 total)