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

    • 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 dates
    myDayNumbers as
    (
    select 0 as num
    union all
    select num + 1 from myDayNumbers
    where num < datediff(day,@startDate,@endDate)
    ),
    -- create a mini calendar table
    -- by converting the numeric offsets to set of dates
    myDays as
    (select dateadd(day,num,@startDate) as dt from myDayNumbers
    )
    -- using calendar table, get required 'goal' date pairs using simple 'group by' query
    select min(dt) as StartDate, max(dt) as EndDate
    from myDays
    group by year(dt)
    -- if can be more than 32767 days between start and end dates then set MAXRECURSION to 0
    option (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_diff
    6

    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_year
    from dbo.fnTally(0, datediff(year, @startDate, @endDate)) fn;
    start_dttally_nstart_dt_plus_year
    2015-01-0102015-01-01
    2015-01-0112016-01-01
    2015-01-0122017-01-01
    2015-01-0132018-01-01
    2015-01-0142019-01-01
    2015-01-0152020-01-01
    2015-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 answer
    from (values (datediff(year, @startDate, @endDate))) yr(dt_diff)
    cross apply dbo.fnTally(0, yr.dt_diff) fn
    order by start_dt;
    start_dttally_nanswer
    2015-01-0102015-12-31
    2015-01-0112016-12-31
    2015-01-0122017-12-31
    2015-01-0132018-12-31
    2015-01-0142019-12-31
    2015-01-0152020-12-31
    2015-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

  • Alright, thank you for your answer

  • 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.

    How to post questions to get better answers faster
    Managing Transaction Logs

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

You must be logged in to reply to this topic. Login to reply