asked
Select max datestamp of each day of 1 month
If I have a query with information for a month. And I want to select only the maximums milliseconds of each day in the month. How can I do this? For example my query displays this: **Date-Stamp** 2013-09-05 02:00:05.477 / 2013-09-04 19:28:24.173 / 2013-09-04 19:21:55.320 / 2013-09-04 09:00:06.207 / 2013-09-02 17:32:23.440 Of this data I want to display only this: 2013-09-05 02:00:05.477 / 2013-09-04 19:28:24.173 / 2013-09-02 17:32:23.440 Is their a specific function to do this?
--------- Set up some test data --------- Conditionally drop temp table IF OBJECT_ID('tempdb..#my_dates') IS NOT NULL DROP TABLE #my_dates; --------- 864000 seconds is 10 days so this gives you a random selection of datetimes between 10 days ago and now. SELECT top 1000 dateadd(ss,-abs(checksum(newid()))%864000,GETDATE()) as my_datetime INTO #my_dates FROM sys.all_objects --This does nothing other than ensure we return 1000 (more if you want) rows. ; --------- Now to hopefully answer your question SELECT MAX(dt.my_datetime) as max_datetime_per_day --get the highest datetime per group (per day in this case) FROM #my_dates dt GROUP BY DATEADD(dd,0,datediff(dd,0,dt.my_datetime)) --Set the time component back to 0:00 ORDER BY max_datetime_per_day ;
16 People are following this question.
