access SQL query - group by date range (after midnight problem)

Need to populate a list of days with # of recordings for each day grouped by day. Here is my problem: all data that was recorded after midnight is going to the next day: i need it to go to the previous day .

I tried this:

SELECT RecordedOnDate, COUNT(RecordedOnDate) AS RowCount, COUNT(SongID) AS [Total Amount]
FROM InfoTable
WHERE (RecordedOnDate > DateAdd('h', 5, #1/23/2011#)) AND (RecordedOnDate < DateAdd('h', 5, #1/24/2011#))
GROUP BY RecordedOnD开发者_StackOverflow社区ate
ORDER BY RecordedOnDate DESC

But cannot GROUP BY Thanks

---

What you want to do is offset the times by 5 hours so they "appear" on the previous day.

SELECT
    DateAdd('h', -5, RecordedOnDate) RecordedOnDate,
    COUNT(RecordedOnDate) AS RowCount,
    COUNT(SongID) AS [Total Amount]
FROM InfoTable
WHERE (RecordedOnDate > DateAdd('h', 5, #1/23/2011#)) AND (RecordedOnDate < DateAdd('h', 5, #1/24/2011#))
GROUP BY DateAdd('h', -5, RecordedOnDate)
ORDER BY RecordedOnDate DESC

Fix your WHERE clause as you require, but it will group by 5am to 5am the next day as "today"

---

Will this work? I'm trying to shift first then group by and aggregate by the shifted and rounded date. Add/update WHERE clause to filter for date range in interest.

;WITH Shifted(sday, smonth, syear)
AS (
  SELECT 
    DatePart(day, DateAdd('h', -5, RecordedOnDate)) sday,
    DatePart(month, DateAdd('h', -5, RecordedOnDate)) smonth,
    DatePart(year, DateAdd('h', -5, RecordedOnDate)) syear
  FROM Table
)
SELECT 
  -- recreate the date
  DateAdd(day, sday - 1, DateAdd(month, smonth - 1, DateAdd(Year, syear - 1900, 0))) RD,
  -- aggregate the count for the shifted date
  COUNT(*)
FROM Shifted
GROUP BY 
  DateAdd(day, sday - 1, DateAdd(month, smonth - 1, DateAdd(Year, syear - 1900, 0)))