How to count those registered on a given datetime-CodePudding

I need to count all records created on a x date, but the table column is not of type date, it is datetime

SELECT COUNT(id)
FROM dbo.Customers
WHERE (SELECT registered_date CONVERT(DATE, GETDATE()) FROM dbo.Customers)

I am trying to convert datetime column to date but I get this error:

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'CONVERT'

CodePudding user response：

Ideally you want your predicate to be sargable, I suspect you need something like

select count(*)
from dbo.Customers
where Registered_date >= convert(date, getdate()) 
      and Registered_date < convert(date, dateadd(day, 1, getdate()));

CodePudding user response：

I'm not sure what the subquery is for. Would this work:

SELECT COUNT(id)
FROM dbo.Customers
WHERE CONVERT(DATE, registered_date) = CONVERT(DATE, GETDATE())

Edited to add the convert for registered_date.