Aha, light bulb moment! I knew I was missing something there, as much as anything from skim reading rather than properly looking at it.

So, if we were splitting into months it would look like this with 13 partitions, twelve that will hold data that we are currently processing, and a thirteenth for the one we will be purging:-

--show rolling partitions

DECLARE @Tables TABLE

(MonthDate datetime2(0) NOT NULL)

INSERT INTO @Tables

SELECT TOP 13 DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY object_id) -1, '2015-01-01') AS MonthDate

FROM sys.columns AS c

SELECT MonthDate

, ABS(DATEDIFF(MONTH,0,MonthDate)%13) + 1

FROM @Tables

--code to define the partition on the table

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

Or the same for quarters but using modulo 5 and quarter in the datefunctions instead of month.

I guess the only risk is that if for some reason the job that runs the partition switch fails/doesn't run for some reason, then you would end up with data going into a partition that was already populated with old data?