SQL Server How can we write a sql function which returns the records where getdate() is between From-CodePudding

How can we write a sql function which returns the records where getdate() is between FromDate and Todate as follows

if getdate() exists between FromDate and Todate then return 
elseif DateAdd(Year, -1, getdate()) is between FromDate and Todate.
elseif DateAdd(Year, -2, getdate()) is between FromDate and Todate.

Mytable

ProductID FromDate Todate  
A   1/2/2022  1/2/2023
A   1/2/2021  1/2/2022
A   1/2/2020  1/2/2021
A   1/2/2019  1/2/2020
B   1/2/2020  1/2/2021
B   1/2/2019  1/2/2020
C   1/2/2019  1/2/2020
C   1/2/2018  1/2/2019

Suppose getdate() is 1/10/2022 the result should be as below

ProductID FromDate Todate  Active
A   1/2/2021  1/2/2022
B   1/2/2020  1/2/2021
C   1/2/2019  1/2/2020

CodePudding user response：

I believe you mean "How can I return records from this table where either today's date, one year ago or two years ago are between the from and to dates?"

In which case, you were close, but this should work:

WITH dates
AS
(
SELECT DATEADD(Year,i,CAST(GETDATE() AS Date)) AS datecheck
FROM (VALUES (0),(-1),(-2)) t(i)
)
SELECT ProductID, FromDate, ToDate
FROM Mytable, dates
WHERE datecheck BETWEEN FromDate AND ToDate;