Keeping Most Recent Record from Today's Date in Table

Question

I need help with an update/delete statement that I plan to use in SSIS. I have the following table of records:

Record   DateCreated   TimeCreated
1          3/7/19       11:00AM
2          3/7/19       8:00AM
3          2/5/19       9:00AM
4          2/6/19       10:00AM

I want to update the table so that I keep the most recent record from Today's Date (3/7/19) but remove the older record from Today's Date.

The table should look like this after the update:

Record   DateCreated   TimeCreated
1          3/7/19       11:00AM
3          2/5/19       9:00AM
4          2/6/19       10:00AM

If there are more than 2 records on Today's Date (e.g. 3, 4, etc), I want it to keep the only most recent one.

Can anybody please help me with the update/delete statement needed for this?

Answer 1

You can use an updatable CTE:

with todelete as (
      select t.*,
             row_number() over (partition by datecreated order by timecreated desc) as seqnum
      from t
     )
delete from todelete
    where seqnum > 1 and
          datecreated = convert(date, getdate());