Map fiscal years using a date column from one table and taking start and end date from another table

I have two tables in SQL Server. The first table contains a Date column. The second table contains start date and end date for various fiscal years and another column denoting fiscal years (that goes like fy21, fy22, and so on). I want to map the Date in table 1 to the FYs in the second table by checking if the Date lies between the start date and end dates in the second Table. How can that be done in SQL SERVER? The first table has 1 column Date. The second table has 3 columns, start_date, end_date, fy. The rows look like row1: 01-09-18, 31-08-19,fy19 row2: 01-09-19, 30-08-20,fy20 row3: 31-08-20 ,27-10-21, fy21 row4: 28-10-21, 31-11-22,fy22

Answer

Short answer is you join – a simple join at that. There are MANY examples of calendar tables along with examples of how they are used. And for the sake of humanity, NEVER use two digit years.

One example using names that are not nonsense (e.g., first table):

select ... 
from dbo.calendar as cal 
inner join dbo.fyperiods as fy 
on cal.date between fy.start and fy.end
order by ...;

Not certain what you mean by “checking”. The above query can be rearranged so that you find rows that do not exist in a fiscal year by changing the inner join to a left join (with a filter) or by using an EXISTS clause as a filter.