sgmunson (7/10/2014)

---

There's a much simpler query:

DECLARE @DATE AS datetime = '07/10/2014';

DECLARE @TIME_START_INT AS int = 820;

DECLARE @TSV AS varchar(4) = RIGHT('00' + CAST(@TIME_START_INT AS varchar(4)), 4);

DECLARE @TIME_END_INT AS int = 1000;

DECLARE @TEV AS varchar(4) = RIGHT('00' + CAST(@TIME_END_INT AS varchar(4)), 4);

DECLARE @START AS datetime = @DATE + CAST(LEFT(@TSV, 2) + ':' + RIGHT(@TSV, 2) + ':00' AS time);

DECLARE @END AS datetime = @DATE + CAST(LEFT(@TEV, 2) + ':' + RIGHT(@TEV, 2) + ':00' AS time);

DECLARE @INTERVAL AS int = 20;

WITH Tally AS (

SELECT 1 AS N

UNION ALL

SELECT N + 1

FROM Tally

WHERE N + 1 < 10001

)

SELECT DATEADD(mi, @INTERVAL * N, @START) AS THE_TIME

FROM Tally

WHERE DATEADD(mi, @INTERVAL * N, @START) <= @END

OPTION (MAXRECURSION 10000)

Janet Barnett (7/10/2014)

---

Looking for assistance in returning multiple entries from a time span. I have a date, start-time, end-time and duration. I need the start-times separated in a list. It's fine if temp tables are needed - I have that clearance.

Entry might look like:

(datetime) date: 7/10/2014

(int) start-time 820

(int) end-time 1000

(int) duration 20

The result would needed:

(datetime) 7/10/2014 08:20:00

(datetime) 7/10/2014 08:40:00

(datetime) 7/10/2014 09:00:00

(datetime) 7/10/2014 09:20:00

(datetime) 7/10/2014 09:40:00