Query to produce time pattern with 5 minutes interval

–Use CTE to in a recursive way to generate the list

;With mycte

As

(

–Define 1 as start as Time_ID and retrieve the part in the format we want with datediff and dateadd function

SELECT 1 as Time_ID, RIGHT(CONVERT(VARCHAR(16),DATEADD(day, DATEDIFF(day,0,GETDATE()),0),120),5) as Time_Slot

UNION ALL

SELECT Time_ID+1 ,RIGHT(CONVERT(VARCHAR(16),DATEADD(minute, Time_ID*5,DATEADD(day, DATEDIFF(day,0,GETDATE()),0) ) ,120),5)

FROM mycte

WHERE DATEADD(minute, Time_ID*5,DATEADD(day, DATEDIFF(day,0,GETDATE()),0))

<DATEADD(day,DATEDIFF(day,0,GETDATE())+1,0)

–You can get more information about recursive CTE from MSDN:

http://msdn.microsoft.com/en-us/library/ms186243.aspx

)

–Create a NewTable with data and structure from the CTE table mycte

SELECT * INTO NewTable FROM mycte

 

–The default recursion is upto 100 level. We explicit the MAXRECURSION number to 300 for our need here

OPTION (MAXRECURSION 300)

 

–Retrieve data from the Newtable

SELECT * FROM NewTable