Linq Group by whole day

  • Thread starter Thread starter Brett Kelts
  • Start date Start date
B

Brett Kelts

Can anybody help me figure this out. I use this sort of query in a
bunch of different places. Essentially I want to sum by an entire day.

SELECT
CONVERT(SMALLDATETIME, CONVERT(CHAR(10), StartDate, 102)) as
StartDay,
Count(CourseID) as CountCourse
FROM Course
GROUP BY CONVERT(SMALLDATETIME, CONVERT(CHAR(10), StartDate, 102))
ORDER BY CONVERT(SMALLDATETIME, CONVERT(CHAR(10), StartDate, 102))

The results will be something like this

StartDay CountCourse
1/1/2008 50
1/2/2008 60
1/3/2008 55

etc.

Any thoughts
 
Brett Kelts said:
Can anybody help me figure this out. I use this sort of query in a
bunch of different places. Essentially I want to sum by an entire day.

SELECT
CONVERT(SMALLDATETIME, CONVERT(CHAR(10), StartDate, 102)) as
StartDay,
Count(CourseID) as CountCourse
FROM Course
GROUP BY CONVERT(SMALLDATETIME, CONVERT(CHAR(10), StartDate, 102))
ORDER BY CONVERT(SMALLDATETIME, CONVERT(CHAR(10), StartDate, 102))

The results will be something like this

StartDay CountCourse
1/1/2008 50
1/2/2008 60
1/3/2008 55
Click to expand...

In your C# code, try grouping by StartDate.Date.
 
I hadn't yet needed to do this, but I'm kinda glad it works! Shame about
all those DATEADDs though... I've just run a test, and a

CAST(FLOOR(CAST(x as float)) as datetime)

is twice as quick... 2594ms [1310ms] vs 4579ms [2295ms]
(total-CPU-time [elapsed-time], based on 3773567 rows with a
non-clustered index on the time column, having pre-fetched the data
[logical reads], on a multi-core server).

OK, yes it is a blatant abuse of knowing the data-structure, but should
be fine for LINQ-to-SQL at least ;-p

I might drop a message on the LINQ General forum...

Marc
 
Back
Top