Display all records whether DateTime exit or not from two table using JOIN in SQL Server

Hi akhter,

Refer below sample.

SQL

CREATE TABLE #EmpMaster(EnrollNumber int,EmpName varchar(max))
CREATE TABLE #AttendenceLOG(ID int,EnrollNumber int,A_Date datetime)
INSERT INTO #EmpMaster values(1001, 'Andrea')
INSERT INTO #EmpMaster values(1002, 'pandeyism')
INSERT INTO #EmpMaster values(1003, 'dharmendr')
INSERT INTO #EmpMaster values(1004, 'Mudassar')
INSERT INTO #EmpMaster values(1005, 'Akhter')
INSERT INTO #EmpMaster values(1006, 'Rehan')
INSERT INTO #EmpMaster values(1007, 'Irfan')
INSERT INTO #AttendenceLOG values(1,1001, '2019-05-28 17:18')
INSERT INTO #AttendenceLOG values(2,1002, '2018-05-28 17:18')
INSERT INTO #AttendenceLOG values(3,1003, '2018-05-28 17:18')
INSERT INTO #AttendenceLOG values(4,1006, '2018-05-28 17:18')
SELECT em.EnrollNumber,em.EmpName,ISNULL(Convert(VARCHAR,alg.A_Date,4),'') Date FROM #EmpMaster em
LEFT JOIN #AttendenceLOG alg ON alg.EnrollNumber = em.EnrollNumber
WHERE alg.A_Date BETWEEN @StartDate AND @EndDate OR alg.A_Date IS NULL
DROP TABLE #EmpMaster
DROP TABLE #AttendenceLOG

Output