locked
Group by date sql compact RRS feed

  • 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;
    Tuesday, June 28, 2016 1:10 PM

Answers

  • User1853794821 posted

    try using

    CONVERT(NVARCHAR(8), DateCreated,101)

    instead of

    DateCreated

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 28, 2016 1:24 PM

All replies

  • User379720387 posted
    Select Count(*), CONVERT(date, DateCreated) from where group by CONVERT(date, DateCreated)
    Tuesday, June 28, 2016 1:16 PM
  • User-1327394822 posted

    it says 

    "The specified data type in not valid" = date

    Tuesday, June 28, 2016 1:20 PM
  • User1853794821 posted

    try using

    CONVERT(NVARCHAR(8), DateCreated,101)

    instead of

    DateCreated

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 28, 2016 1:24 PM
  • User-1327394822 posted

    still the same

    Tuesday, June 28, 2016 1:26 PM
  • 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)

    Tuesday, June 28, 2016 5:44 PM