Get the smallest datetime value for each day in SQL database

Question

I have a database that are storing datetime values every minute and I wanna get the smallest value for each day.

My query looks like this:

SELECT  lVehicleId, dtVehicleHistoryDate 
FROM  VehicleHistory
WHERE  lVehicleId = 1146 AND dtVehicleHistoryDate > '2016-01-01 00:00:00' AND dtVehicleHistoryDate < '2016-01-31 00:00:00' AND ((dtTimeReceived - dtVehicleHistoryDate) > '00:30:00')

And my values is disposed like this:

Id      History date

1146    2015-01-26 23:03:50.000

1146    2015-01-26 23:04:50.000

1146    2015-01-26 23:05:50.000


1146    2015-02-19 01:33:42.000

1146    2015-02-19 01:34:42.000

1146    2015-02-19 01:35:42.000

1146    2015-02-19 01:36:42.000

By "smallest value" do you mean just truncate the time so you get midnight on that date? — Jamiec, Mar 17 '16 at 14:26
Possible duplicate of [How can I truncate a datetime in SQL Server?](http://stackoverflow.com/questions/923295/how-can-i-truncate-a-datetime-in-sql-server) — Jamiec, Mar 17 '16 at 14:27
In this case i wanna have the result 1146 2015-01-26 23:03:50.000 and 1146 2015-02-19 01:33:42.000 — hessel, Mar 17 '16 at 14:32

score 0 · Accepted Answer · answered Mar 17 '16 at 14:28

0

You can group by id and trunc date value:

SELECT t.id,cast(t.history_date as DATE),min(t.history_date)
FROM YourTable t
GROUP BY t.id,cast(t.history_date as DATE)

answered Mar 17 '16 at 14:28

sagi

40,026
6
59
84

naaaah im about to post my answer :( – Anonymous Duck Mar 17 '16 at 14:30

Get the smallest datetime value for each day in SQL database

1 Answers1

Linked