SELECT and GROUP BY part of a date/time field?

[lwales]

Is it possible to select only the date part of a date time field and also use it to group by and order by? It's the bookings.booked_date_time field. (I think I'm now at the point of completely over complicating it - no doubt there's a simpler solution)

SELECT Count(bookings.id) AS CountOfid, bookings.offer_id, Sum(bookings.num_seats_booked) AS CountOfseats, bookings.booked_date_time, offer_timeslots.timefrom, offer_timeslots.timeto, Sum(bookings.noofpromos) AS CountOfpromobooked
FROM (bookings INNER JOIN offers on offers.id = bookings.offer_id) INNER JOIN offer_timeslots on offer_timeslots.id = bookings.timeslot_id
WHERE bookings.enabled=1 AND offers.id=offerid
GROUP BY bookings.booked_date_time, bookings.offer_id, offer_timeslots.timefrom, offer_timeslots.timeto
ORDER BY bookings.booked_date_time DESC

 

[r937]

Is it possible to select only the date part of a date time field and also use it to group by and order by?

yes

you did not say which database you're using, but i'm going to take an educated guess based on the parentheses you have around your join tables and say it's access

select otherstuff
, Format(bookings.booked_date_time,"yyyy/mm/dd&quot
from ...
where ...
group by otherstuff
, Format(bookings.booked_date_time,"yyyy/mm/dd&quot
order by otherstuff
, Format(bookings.booked_date_time,"yyyy/mm/dd&quot

by the way, you have a join condition in your WHERE clause, it's not a good idea to do that if you're going to use JOIN syntax

rudy

 

[lwales]

thanks very much rudy, I'll give it a go