SQL code to update multiple time entries in a table by 5 minutes

Question

I'm a novice at SQL and wondering if anyone knew of a way to update multiple time entries in a table in increments of 5 minutes? The table is not very large and I would like the date to start off for example today at 800AM and then increment to the next entry with a new time of 805AM.

If you need more info please let me know. The column is datetime format. Here is a sample from the table 2016-12-14 10:00:00.000.

So I would want to update the SCHEDTIME Column from this > initial table

To this > with dates changed

Answer 1

1. see this post about generating date ranges
2. see the second answer by abe-miessler
3. Change the 5th line to this to generate a list of 5-minute increment datetimes between the given dates:

[Date] = DATEADD(MINUTE, 5, [Date])

4. Update the MAXRECURSION as needed

Answer 2

If you have continuous id from 1...n then you can do something like:

DECLARE @StartDate DATETIME='2016-12-14 10:05:00'

Update [YourTable]
SET [Date] = DATEADD(MINUTE,(id-1)*5, @StartDate )

The above code will add 5 mins for id 2,10 mins for id 3 and so on.

In case your ids are not continuous:

 DECLARE @StartDate DATETIME='2016-12-14 10:05:00'

;WITH ToUpdate
AS(
 SELECT *,ROW_NUMBER() OVER (ORDER BY Id) AS RN
 FROM [YourTable]
)

UPDATE ToUpdate
SET DATE=DATEADD(MINUTE,(RN-1)*5, @StartDate )