Query in SQL GROUP BY period

I need to make a simple query to count how many record i have in a specific period of time like day, month, year, pseudo example:

SELECT COUNT( EmployeeID)
FROM  HumanResources.Employees
WHERE   Hire Date.YEAR = 'year开发者_开发知识库'
GROUP BY Hire Date.YEAR

this code does not works, what should be?

You can use the YEAR() and MONTH() functions to split information into periods.

For example:

SELECT YEAR(Hire Date), MONTH(Hire Date), COUNT( EmployeeID) FROM  
HumanResources.Employees WHERE   YEAR(Hire Date) = 'year' 
GROUP BY YEAR(Hire Date),MONTH(Hire Date) 

Here is the query

select COUNT(EmployeeID)
from HumanResources.Employees
where year(Hire Date)=2011 
  and month(Hire Date)=03
group by Hire Date

If you want to query between date range

select COUNT(EmployeeID)
from HumanResources.Employees
where Hire date between '2011/03/01' and '2011/03/03'
group by Hire Date

Take a look at the SQL Server DatePart function

WHERE DatePart(y, [Hire Date]) = 2011
GROUP BY DatePart(y, [Hire Date])

You can substitute y with a number of dateparts (month, quarter, week, day, dayofweek etc)

Assuming you can establish the start and end dates of the range you want to count the records in then I would use the BETWEEN operator.

SELECT COUNT(EmployeeID)
FROM   Employees emp
WHERE  HireDate BETWEEN @rangeStartDate AND @rangeEndDate

If working with different ranges is part of what you need, and assuming you are doing this purely in T-SQL, then you could use the DATEADD function to calculate the range end date based on an specific offset and a known range start date.

e.g. to find count of employees hired in 2010:

SELECT COUNT(EmployeeID) 
FROM HumanResources.Employees 
WHERE [Hire Date] >= '20100101' AND [Hire Date] < '20110101'

Doing it this way will mean if there is an index on Hire Date, it will be used (better performance). As soon as you start manipulating the column you are searching on (e.g using YEAR() or MONTH() functions), you lose the ability for an index to be used.

Is this the kind of thing you're after?

If what you are looking for is to convert it to LINQ-to-SQL, following should work:

var count = (from e in HumanResourcesDataContext.Employees
             where e.HireDate.Year = 2011 // or whatever you want it to be
             select e).Count();