SQL Server 2017

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

CREATE TABLE T1
	([val] int, [createDate] datetime)
;
	
INSERT INTO T1
	([val], [createDate])
VALUES
	(1, '2019-11-03 00:00:00'),
	(2, '2019-11-03 00:00:00'),
	(3, '2019-11-02 00:00:00'),
	(4, '2019-11-02 00:00:00'),
	(5, '2019-11-01 00:00:00')
;
5 rows affected

CREATE TABLE T2
	([val] int, [createDate] datetime)
;
select Convert(date, dateadd(day,-1,getdate()))  


(No column name)


02/11/2019 00:00:00


insert into T2
select * from T1
where createDate = Convert(date, dateadd(day,-1,getdate()))  
2 rows affected

select * from T2


val
createDate


3
02/11/2019 00:00:00

4
02/11/2019 00:00:00