SQL Query to match date in dd/MM/yyyy format in Where Clause in SQL Server

Here I have created SQL Script, I hope this will help you out.

SQL Script

DECLARE @temp AS TABLE(Id INT IDENTITY(1,1),Name VARCHAR(20),CreatedDate DATETIME)
 
INSERT INTO @temp(Name,CreatedDate) VALUES('David','2015-06-25 19:01:47.767')
INSERT INTO @temp(Name,CreatedDate) VALUES('Kevin','2015-06-24 18:01:47.767')
INSERT INTO @temp(Name,CreatedDate) VALUES('Jhon','2015-06-24 11:01:47.767')
INSERT INTO @temp(Name,CreatedDate) VALUES('Peter','2015-06-22 09:01:47.767')
 
DECLARE @Day CHAR(10) = 'Yesterday'

SELECT * FROM @temp WHERE CONVERT(VARCHAR(10),CreatedDate,103) = (CASE WHEN UPPER(@Day) = 'TODAY' THEN  CONVERT(VARCHAR(10),GETDATE(),103)
											  ELSE CONVERT(VARCHAR(10),DATEADD(DD,-1,GETDATE()),103) END)

Screenshot