How to count no of record from a particular month in SQL

OK, in that case try the datepart() function extracting the month portion from a datetime:

DATEPART(month, getdate())

DATEPART (Transact-SQL) | Microsoft Docs[^]

you may try this:

And make sure you add year as well. else if you have records for 3 years in table it might end up providing you count for last 3 years for the provided month.

select  count(*) as present from attendance  where present='P'   and name='dummmy' and
 DATEPART(mm, [date]) = DATEPART(mm, getdate())
AND DATEPART(yy, [date]) = DATEPART(yy, getdate())

You can't use EOMONTH and DATETIMEFROMPARTS since they arrived with SQL Server 2012, but MONTH and YEAR came with SQL SERVER 2008.
So this should work:

select  count(*) as present
from    attendance
where   present='P'
    and name='dummmy'
    AND MONTH([date]) = MONTH(SYSDATETIME)
    AND YEAR([date]) = YEAR(SYSDATETIME)

which will perform poorly since it cannot use any indexes on date.
But you wouldn't have been able to use that anyway since from your example it also seems you have stored the date as a varchar in italian format.
So to fix that error we need to add a conversion:

select  count(*) as present
from    attendance
where   present='P'
    and name='dummmy'
    AND MONTH(CONVERT(DATE,[date],105)) = MONTH(SYSDATETIME)
    AND YEAR (CONVERT(DATE,[date],105)) = YEAR(SYSDATETIME)

As you are using sql server 2008 , here i create query with help of sql function month(),year()and getdate() ,

Try this query

SELECT  COUNT(*) as present from attendance
WHERE  present='P'and name='dummmy' and MONTH([date]) = MONTH(GETDATE()) and
YEAR([date]) = YEAR(GETDATE())