Count number of Distinct days in query
sql-server-2008, t-sql
Solution
You can `CAST` as date
SELECT COUNT(DISTINCT CAST(StartDate AS DATE))
FROM Stats
WHERE StartDate >= '20130101' AND StartDate < '20140101'
Also use an unambiguous date format such as `yyyymmdd` and `>= <` not `BETWEEN`.
Your current query would include the 31st December if there was a row with exactly the value `20131231 00:00:00` but not any with different times on that date. I doubt that is intentional.
Problem
We have a table that has a `StartDate` field which holds a type of datetime. There are thousands of records and I am looking for a way to find the number of days within a given result returned from this table. For instance, if my table had this data: ``` ID | StartDate -------------- 1 01/01/2013 09:34:54 2 01/01/2013 11:23:21 3 04/11/2013 14:43:23 4 04/11/2013 17:13:03 5 04/25/2013 18:02:59 6 07/21/2013 02:56:12 7 10/01/2013 19:43:10 ``` Then the query should return 5 as the 2 dates on 01/01/2013 count as 1 and the same for 04/11/2013. The only SQL I've been able to come up with is: ``` SELECT COUNT(DISTINCT(DATEPART(DAY, StartDate))) FROM Stats WHERE StartDate BETWEEN '01/01/2013' AND '12/31/2013' --This is just for filtering ``` But this returns 4 because it doesn't take the month into account. Any help is appreciated.