Determine Date This Year Based off Future Date

Question

A bit of an odd business request just came up. We have contracts that span multiple years. The system maintains the contract's End Date. I have to determine the End Date's same calendar date, but for the current year if it hasn't passed this year, or the next year if it has passed this year.

For example, as of today, 2020-01-23:

Case 1:

Input: Contract End Date of 2022-07-01

Expected Output: 2020-07-01 (because 2020-07-01 hasn't passed yet)

Case 2:

Input: Contract End Date of 2022-01-05

Expected Output: 2021-01-05 (because 2020-01-05 has already passed)

I've never posted a question on SO without some "what did you try so far" code, but I'm at a complete loss here. I imagine I could string-manipulate the heck out the dates, and do some funky things to find out if the date passed, etc. But is this requirement anyone has ever worked on, or is up for a challenge? :-) Even guidance an not code would help. Then I could edit the question with what I come up with.

EDIT:

Rephrasing the question in another way, given a future date, how can I determine the next occurrence of that day/month of that future date, based off "today".

On 2020-01-23, take 2022-07-01 and get to 2020-07-01 (the next occurrence of July 1)

On 2020-01-23, take 2024-01-05 and get to 2021-01-05 (the next occurrence of Jan 5)

Will the input Contract End Date ever be a date that already has passed? — jarlh, Jan 23 '20 at 20:54
No. I'm limiting the data to active contracts only. The End Date could theoretically be tomorrow, or in 10 days, in 4 years, etc. — HardCode, Jan 23 '20 at 20:58
@jarlh, it's showing this error: Msg 8117, Level 16, State 1, Line 11 Operand data type date is invalid for subtract operator. — HardCode, Jan 23 '20 at 21:11

Zohar Peled · Answer 1 · 2020-01-23T21:43:58.823

With the help of DateAdd, cross apply and case - the solution is simpler than you might think.
First, create and populate sample table(Please save us this step in your future questions):

DECLARE @T AS TABLE
(
    ContractEndDate date
);

INSERT INTO @T (ContractEndDate) VALUES
('2022-07-01'), 
('2023-01-05');

Then, I've used a variable to hard code the date, but naturally you should be using GetDate() for this one:

DECLARE @Today Date = '2020-01-23';

The query itself is this:

SELECT  ContractEndDate, -- This is just for reference
        ThisYear, -- This is just for reference
        -- add a year in case we've passed the date
        CASE WHEN (ThisYear < @Today) 
        THEN 
            ThisYear 
        ELSE 
            DATEADD(YEAR, 1, ThisYear) 
        END As Result
FROM @T
CROSS APPLY
(
    -- Get the date of the contract end in the current year
    SELECT CAST(
            DATEADD(YEAR, YEAR(@Today)-YEAR(ContractEndDate), ContractEndDate)
        As Date) As ThisYear
) t

Results:

ContractEndDate     ThisYear    Result
2022-07-01          2022-07-01  2021-07-01  
2023-01-05          2023-01-05  2020-01-05

score -1 · Answer 2 · answered Jan 23 '20 at 20:59

-1

The question is a bit unclear however, you can select only month and day and try to match dates by them alone:

SELECT FORMAT (getdate(), 'dd-MM') as date

It would be better to have schema as well as query you are building so we understand what is the problem.

answered Jan 23 '20 at 20:59

Milos K

1,009
8
5

Schema wouldn't matter, as it's just a Date data type field. – HardCode Jan 23 '20 at 21:06

Determine Date This Year Based off Future Date

Case 1:

Case 2:

EDIT:

2 Answers2