SQL Server 2019

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

create table test (Tarihdtl nvarchar(20),
   urun nvarchar(20),
   baslangic nvarchar(20) null,
   bitis nvarchar(20) null);

insert into test (Tarihdtl, urun, baslangic, bitis ) values 
	('15.05.2011', 'A ürünü', '01.05.2009', '15.11.2009'),
	('15.05.2011', 'B ürünü', '16.11.2009', '02.05.2009'),
	('15.05.2011', 'C ürünü', null, '02.05.2009'),
	('15.05.2011', 'D ürünü', '16.11.2009', null),
	('15.05.2011', 'E ürünü', null, null),
	('15.05.2011', 'F ürünü', '', '02.05.2009'),
	('15.05.2011', 'G ürünü', '16.11.2009', ''),
	('15.05.2011', 'H ürünü', '', ''),
	('15.05.2011', 'I ürünü', 'xx', '02.05.2009'),
	('15.05.2011', 'J ürünü', '16.11.2009', 'xx'),
	('15.05.2011', 'K ürünü', 'xx', 'xx'),
	('15.05.2011', 'L ürünü', '', '15.11.2009'),
	('15.05.2011', 'M ürünü', '01.05.2009', ''),
	('15.05.2011', 'N ürünü', '', '');

WITH dtConverted1 (t, bs, bt, tarih, urun, baslangic, bitis)
AS (SELECT TRY_CONVERT(DATE, Tarihdtl, 104),
           TRY_CONVERT(DATE, baslangic, 104),
           TRY_CONVERT(DATE, bitis, 104),
           Tarihdtl, urun, baslangic, bitis
    FROM test),
     dtConverted (t, bs, bt, tarih, urun, baslangic, bitis)
AS (SELECT CAST(SUBSTRING(CONVERT(CHAR(8), COALESCE(t, '20000101'), 112), 5, 4) AS INT),
           CAST(SUBSTRING(CONVERT(CHAR(8),
                COALESCE( CASE WHEN bs > '19000101' THEN bs END,'20000101'), 112 ), 5, 4) AS INT),
           CAST(SUBSTRING(CONVERT(CHAR(8),
                COALESCE( CASE WHEN bt > '19000101' THEN bt END,'20001231'), 112 ), 5, 4) AS INT),
           Tarih, urun, baslangic, bitis
    FROM dtConverted1)
SELECT *
FROM dtConverted
WHERE t BETWEEN bs AND bt;

t	bs	bt	tarih	urun	baslangic	bitis
515	501	1115	15.05.2011	A ürünü	01.05.2009	15.11.2009
515	101	1231	15.05.2011	E ürünü	null	null
515	101	1231	15.05.2011	H ürünü
515	101	1231	15.05.2011	K ürünü	xx	xx
515	101	1115	15.05.2011	L ürünü		15.11.2009
515	501	1231	15.05.2011	M ürünü	01.05.2009
515	101	1231	15.05.2011	N ürünü