I have a requirement like have to pull all records in the date range the user selected, selecting all employees who started from 15-Jan-2011 to 20-Aug-2011 and group by date.
How should I write SQL query for this:
SELECT * FROM employees WHERE startdate >= '15-jan-2011' AND startdate <= '20-aug-2011' GROUP BY startdate 2 Answers
Absolutely. It will result in filtering the records on your date range and then grouping it by each day where there is data.
It should be noted that you will only be able to select the startdate and then whatever aggregates you're calculating. Otherwise, it should work perfectly fine.
For example, this query will give you a count of employees for each startdate:
SELECT startdate, count(*) FROM employees WHERE startdate >= '15-jan-2011' AND startdate <= '20-aug-2011' GROUP BY startdate You can, but the "GROUP BY" clause is used for grouping together sets of rows, so it does not make sense for your question (or anything that involves a "SELECT *").
To answer your question though:
SELECT DATEADD(dd, 0, DATEDIFF(dd,0,StartDate)) AS 'StartDate', <other fields> FROM Employees WHERE StartDate BETWEEN '15-Jan-2011' AND '20-Jan-2011' ORDER BY StartDate Note: the stripping of the time from the date came from here
