SQL Server 2016

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

create table product(product_id int, product_name varchar(10));
insert into product
  select 1,'p1' union all
  select 2,'p2' union all
  select 3,'p3' 
3 rows affected

create table test(product_id  int, test_id int,test_name varchar(50),test_type varchar(50))
insert into test
   select 1,1,'test1.1','duration    ' union all
   select 2,2,'test2.1','intrinsic'    union all
   select 3,3,'test3.1','duration'     union all
   select 2,4,'test2.2','scalability'  union all
   select 3,5,'test3.2','intrinsic'    union all
   select 1,6,'test1.2','duration'     union all
   select 2,7,'test2.3','scalability'  union all
   select 2,8,'test2.4','duration'     union all
   select 3,9,'test3.3','intrinsic'    
9 rows affected

create table test_func(func_id int,test_id int, test_start_date varchar(20),func_type varchar(50))
insert into test_func
   select 1,2,'Jan 18, 2017','basic    ' union all
   select 2,5,'Jun 2, 2018 ','advanced'  union all
   select 3,7,'Mar 9, 2011 ','basic'     union all
   select 4,3,'May 20, 2021','basic'     union all
   select 5,1,'Aug 1, 2020 ','advanced'  union all
   select 6,6,'May 3, 1998 ','advanced'  union all
   select 7,8,'Jul 17, 2017','basic'     union all
   select 8,4,'Apr 3, 2016 ','basic'     union all
   select 9,9,'Dec 21, 2019','basic'     
9 rows affected

with data
      as (
    select p.product_id
          ,tf.test_start_date
          ,tf.test_id
          ,cast(tf.test_start_date as date) as test_start_date_dt
          ,row_number() over(partition by p.product_id order by cast(tf.test_start_date as date) desc) as rnk
      from product p
      join test t
        on p.product_id=t.product_id
      join test_func tf
        on tf.test_id=t.test_id
         )
    select *
      from data
     where rnk=1


product_id
test_start_date
test_id
test_start_date_dt
rnk


1
Aug 1, 2020
1
2020-08-01
1

2
Jul 17, 2017
8
2017-07-17
1

3
May 20, 2021
3
2021-05-20
1

product_id	test_start_date	test_id	test_start_date_dt	rnk
1	Aug 1, 2020	1	2020-08-01	1
2	Jul 17, 2017	8	2017-07-17	1
3	May 20, 2021	3	2021-05-20	1