Group by date sql compact

Question

User-1327394822 posted

I am trying to group cart entries in my sql ce database as per date, below is the query. 

However, i just want to group by date and not time. I tries some queries but sql ce says "function not supported" etc.

Select Count(*), DateCreated from Cart where CheckedOut = 0 group by datecreated;

Answer 1

User379720387 posted

Select Count(*), CONVERT(date, DateCreated) from where group by CONVERT(date, DateCreated)

Answer 2

User-1327394822 posted

it says 

"The specified data type in not valid" = date

Answer 3

User1853794821 posted

try using

CONVERT(NVARCHAR(8), DateCreated,101)

instead of

DateCreated

Answer 4

User-1327394822 posted

still the same

Answer 5

User1853794821 posted

Select 
Count(*), CONVERT(nvarchar(8), DateCreated, 101) as Dt 
from Cart where CheckedOut = 0 
group by CONVERT(nvarchar(8), DateCreated, 101);

You need to have the group by column(s) match the selected (non-aggregate) columns

See the msdn doc on CONVERT/CAST and, specifically, the style codes (the above example uses 101 for the US standard of mm/dd/yyy)