Get Previous Day Distinct Records in SQL Server

Please refer below code

SQL

DECLARE @Table1 AS TABLE(FName VARCHAR(50),LName VARCHAR(50),Createddate VARCHAR(50))

INSERT INTO  @Table1 VALUES('HGHG','A','24:06:2016:00:10:000')
INSERT INTO  @Table1 VALUES('HGHG','B','25:06:2016:00:10:000')
INSERT INTO  @Table1 VALUES('JHGJ','C','25:06:2016:01:18:000')
INSERT INTO  @Table1 VALUES('JHGJ','CD','25:06:2016:06:10:000')
INSERT INTO  @Table1 VALUES('JHGJ','E','26:06:2016:06:10:000')

DECLARE @Date VARCHAR(10)= '25:06:2016' 

SELECT *
FROM @Table1
WHERE  SUBSTRING(Createddate,1,DATALENGTH(Createddate)-10) = @Date

I hope this will help you out.

Thanks for your response, However query is not working as per my Expectation, Could you please help me

I need Result like this :- 

    ID     FName      LName        Number            CreateDate
    2      AAAAA       A            111111       06-26-2016 01:18:000
    3      CCCCC       C            333333       06-25-2016 06:10:000
    4      DDDDD       D            444444       06-25-2016 06:10:000
    5      EEEEE       E            555555       06-25-2016 23:10:000
    7      FFFFF       F            777777       06-26-2016 00:01:000
    8      GGGGG       G            888888       06-26-2016 16:01:000
    9      HHHHH       H            999999       06-26-2016 23:01:000

    Create Table Users1
    (
    ID int,
    FName varchar (50),
    LName Varchar (50),
    Number varchar(10),
    CreateDate Datetime
    )    
    
    INSERT INTO Users1 Values (1,'AAAA','A','11111','06-25-2016 00:10:765')
    INSERT INTO Users1 Values (2,'AAAA','A','11111','06-26-2016 01:18:000')
    INSERT INTO Users1 Values (3,'CCCC','C','33333','06-25-2016 06:10:000')
    INSERT INTO Users1 Values (4,'DDDD','D','44444','06-25-2016 06:10:000')
    INSERT INTO Users1 Values (5,'EEEE','E','55555','06-25-2016 23:10:000')
    INSERT INTO Users1 Values (6,'CCCC','C','33333','06-25-2016 00:01:000')
    INSERT INTO Users1 Values (7,'FFFF','F','77777','06-26-2016 00:01:000')
    INSERT INTO Users1 Values (8,'GGGG','G','88888','06-26-2016 16:01:000')
    INSERT INTO Users1 Values (9,'HHHH','H','99999','06-26-2016 23:01:000')

Please refer below code

SQL

 Create Table #Users1
    (
    ID int,
    FName varchar (50),
    LName Varchar (50),
    Number varchar(10),
    CreateDate Datetime
    )   
     
    INSERT INTO #Users1 Values (1,'AAAA','A','11111','2016-06-25 00:10:000')
    INSERT INTO #Users1 Values (2,'AAAA','A','11111','2016-06-26 01:18:000')
    INSERT INTO #Users1 Values (3,'CCCC','C','33333','2016-06-25 06:10:000')
    INSERT INTO #Users1 Values (4,'DDDD','D','44444','2016-06-25 06:10:000')
    INSERT INTO #Users1 Values (5,'EEEE','E','55555','2016-06-25 23:10:000')
    INSERT INTO #Users1 Values (6,'CCCC','C','33333','2016-06-25 00:01:000')
    INSERT INTO #Users1 Values (7,'FFFF','F','77777','2016-06-26 00:01:000')
    INSERT INTO #Users1 Values (8,'GGGG','G','88888','2016-06-26 16:01:000')
    INSERT INTO #Users1 Values (9,'HHHH','H','99999','2016-06-26 23:01:000')


	SELECT Id,Fname,Lname,Number,CreateDate FROM 
	(
		SELECT ROW_NUMBER() OVER (PARTITION BY Number ORDER BY ID ) as RowNumber,* FROM #Users1
		WHERE CONVERT(VARCHAR,CreateDate,103) =  CONVERT(VARCHAR,GETDATE()-1,103)
	) as tbl
	WHERE RowNumber = 1

	DROP TABLE #Users1

I hope this will help you out

Thank you so much for your response, here I don't want to hard code any thing like @Date, 

Here is my query, Could you please et me know is that OK.

SELECT CreateDate,FName,LName,Number FROM Users1 WHERE CreateDate in ( SELECT MAX(CreateDate) from Users1 group by Number)

I have modified my previous reply

http://www.aspforums.net/Threads/397943/SQL-Help-Required---Report-contains-all-users-added-the-previous-day---DDMMYYYY0001000-to-2359/Replies/3#Replies