test

create function is_valid_date(@param_date date, @start_date date, @end_date date)
returns bit 
as begin

declare @date_diff int = DATEDIFF(day, @start_date, @param_date)
	
	if @date_diff <= 0
		return 0

	if @end_date is null
		return 1

	set @date_diff = DATEDIFF(day, @end_date, @param_date)

	if @date_diff > 0 
		return 0
	
	return 1
end

go

create table testdatabase
(
	id int primary key identity(1, 1),
	[start_date] date not null,
	[end_date] date null
)

go

insert into testdatabase (start_date, end_date) 
values 
(GETDATE(), null),
(GETDATE(), '2022-04-30')

go

-- Test Case - 1 (end_date = null)

declare @param_date_1 date = '2022-05-15'
declare @param_date_2 date = '2022-04-30'
declare @param_date_3 date = '2022-04-11'

select * 
from testdatabase t 
where 
	t.id = 1 and 
	dbo.is_valid_date(@param_date_1, t.start_date, t.end_date) = 1

select * 
from testdatabase t 
where 
	t.id = 1 and 
	dbo.is_valid_date(@param_date_2, t.start_date, t.end_date) = 1


select * 
from testdatabase t 
where 
	t.id = 1 and 
	dbo.is_valid_date(@param_date_3, t.start_date, t.end_date) = 1

-- Test Case - 2 (end_date = not null)

select * 
from testdatabase t 
where 
	t.id = 2 and 
	dbo.is_valid_date(@param_date_1, t.start_date, t.end_date) = 1

select * 
from testdatabase t 
where 
	t.id = 2 and 
	dbo.is_valid_date(@param_date_2, t.start_date, t.end_date) = 1


select * 
from testdatabase t 
where 
	t.id = 2 and 
	dbo.is_valid_date(@param_date_3, t.start_date, t.end_date) = 1