SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tmp (
    name varchar(255),
    expiry DATE
);
insert into tmp(name, expiry) 
select 'arthur', CONVERT(DATETIME, '2010-01-01', 102)
union all
select 'ben', CONVERT(DATETIME, '2018-05-17', 102)
union all
select 'craig', CONVERT(DATETIME, '2005-12-30', 102)
union all
select 'dean', CONVERT(DATETIME, '2021-09-02', 102)
4 rows affected

select * from tmp


name
expiry


arthur
2010-01-01

ben
2018-05-17

craig
2005-12-30

dean
2021-09-02


SELECT name, expiry
FROM tmp
WHERE expiry < GETDATE()
AND expiry >= CONVERT(DATETIME, '2019-01-01', 102) 


name
expiry


dean
2021-09-02


SELECT name, expiry
FROM tmp
WHERE expiry between CONVERT(DATETIME, '2019-01-01', 102) and GETDATE()


name
expiry


dean
2021-09-02