I'm grabbing the results from a query using a PHP webpage from a MSSQL database of entries between x and y dates. Some of those, as shown below, have multiple times per day. When trying to get an output for each day's start time, i'm unsure how to do that. Would this be best done with SQL query manipulation or in PHP looping?
Actual output:
Apr 16 2019 3:00PM
Apr 19 2019 4:00PM
Apr 20 2019 9:00AM
Apr 20 2019 9:30AM
Apr 20 2019 9:40AM
Apr 20 2019 1:20PM
Apr 21 2019 2:00PM
Desired output:
Apr 16 2019 3:00PM
Apr 19 2019 4:00PM
Apr 20 2019 9:00AM
Apr 21 2019 2:00PM
You could do this via query:
SELECT MIN(CAST(datestr AS datetime)) AS mindatetime
FROM t
GROUP BY CAST(datestr AS date)
Having said that, do not store datetime data as formatted strings, store it as datetime datatype and format it using CONVERT or FORMAT functions. Or PHP.