Get the max Time in a datetime column for specific Hours in the day?

Question

I have a column called SnapshotDate with a snapshot every 30 minutes going back two years (example of this column in link below).

How do I get the max Minute (or Second) entry in the SnapshotDate for when the Hour is either 6 or 15?

Note: The 30 minute interval has changed so many times so I can't specifically use 06:50 for example or 15:50 in the WHERE clause.

I currently have WHERE DATEPART(hh,ys.SnapshotDate) IN (6,15) to only get the SnapshotDate entries in the Hours of 0600 or 1500, but how do I retrieve the max time within these entries so that on a particular day, I only get two results: One at 06:50 and the other at 15:50

Answer 1

I would recommend row_number() for this, as:

select t.*
from (select t.*,
             row_number() over (partition by convert(date, snapshotdate), datepart(hour, snapshotdate)
                                order by snapshotdate desc
                               ) as seqnum
      from t
      where datepart(hour, snapshot) in (6, 15)
     ) t
where seqnum = 1;

Unfortunately, SQL Server does not have a date_trunc() function of any sort. But you can convert to a date and use the hour.