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

Screenshot of the SnapshotDate column

score 0 · Answer 1 · answered Oct 08 '19 at 00:29

Here is a window-function based query that, for each day in your dataset, will give you two records: the last record in the 6h timeslot, and the last record in the 15h timeslot:

SELECT *
FROM (
    SELECT 
        t.*,
        ROW_NUMBER() OVER(
            PARTITION BY YEAR(SnapshotDate), MONTH(SnapshotDate), DAY(SnapshotDate), DATEPART(hh, SnapshotDate) 
            ORDER BY SnapshotDate DESC
        ) rn
    FROM mytable
    WHERE DATEPART(hh, SnapshotDate) IN (6, 15)
) WHERE rn = 1

If you just want the record with the greatest time in the 6h timeslot and the record with the greatest time in the 15h timeslot over the whole table (and not on a daily basis), then you can just remove the PARTITION clause:

SELECT *
FROM (
    SELECT 
        t.*,
        ROW_NUMBER() OVER(ORDER BY SnapshotDate DESC) rn
    FROM mytable
    WHERE DATEPART(hh, SnapshotDate) IN (6, 15)
) WHERE rn = 1

Thank you, I used the first query you provided (identical to the other marked-solution but used the `convert(date,SnapshotDate)` instead of breaking it down to `YEAR`, `MONTH` and `DAY`) and it worked like a charm! — RohRoh, Oct 08 '19 at 02:43

score 0 · Accepted Answer · answered Oct 08 '19 at 00:39

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.

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

2 Answers2