Hi indradeo,
You need to convert the datetime to date as the COMP_LODGE_DATE datatype is Date.
Refer below query.
SQL
Create Table #COMP_LODGE_DATE (
[COMP_ID] INT IDENTITY (1, 1) NOT NULL,
[EMP_ID] NVARCHAR (10) NULL,
[COMP_MESSAGE] NVARCHAR (MAX) NULL,
[STATUS_ID] NVARCHAR (10) DEFAULT ('Open') NOT NULL,
[COMP_LODGE_DATE] DATE DEFAULT (getdate()) NOT NULL,
[CLOSING_DATE] DATE NULL,
[REMARKS] VARCHAR (255) NULL,
[remote_ip] NVARCHAR (50) NULL,
[who] NVARCHAR (50) NULL,
[user_feedback] NVARCHAR (255) NULL,
[type] NVARCHAR (255) NULL,
[ATTEND_DATE] DATETIME NULL,
[ATTEND_BY] VARCHAR (255) NULL,
[Image] VARCHAR (100) NULL
);
INSERT INTO #COMP_LODGE_DATE([EMP_ID],[COMP_MESSAGE],[STATUS_ID],[COMP_LODGE_DATE],[CLOSING_DATE],
[REMARKS],[remote_ip],[WHO],[user_feedback],[type],[ATTEND_DATE],[ATTEND_BY],[Image])
VALUES('1','Hi','Open',GETDAte(),GETDate(),'ok', 'ADMIN','ARJUN','GOOD','SWEETS',GETDATE(),'Rahul','PICS');
INSERT INTO #COMP_LODGE_DATE([EMP_ID],[COMP_MESSAGE],[STATUS_ID],[COMP_LODGE_DATE],[CLOSING_DATE],
[REMARKS],[remote_ip],[WHO],[user_feedback],[type],[ATTEND_DATE],[ATTEND_BY],[Image])
VALUES('2','Hello','Close',GETDATE(),GETDATE(),'Yes', 'ADMIN','ARJUN','VERRY GOOD','Veg',GETDATE(),'Rakesh','PICS');
SELECT COUNT (STATUS_ID) Total FROM #COMP_LODGE_DATE where COMP_LODGE_DATE=CAST(GETDATE() AS DATE)
DROP TABLE #COMP_LODGE_DATE
Output
Total
2
