Thursday, 5 January 2017

Query to produce time pattern with 30 minutes interval

DECLARE @lTimeinterval INT = 30;

WITH mycte
     AS (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 * @lTimeinterval, Dateadd(day, Datediff(day, 0, Getdate()), 0)), 120), 5)
         FROM   mycte
         WHERE  Dateadd(minute, Time_ID * @lTimeinterval, Dateadd(day, Datediff(day, 0, Getdate()), 0)) < Dateadd(day, Datediff(day, 0, Getdate()) + 1, 0))
SELECT Time_Slot
FROM   mycte 

No comments:

Post a Comment

Get all non-clustered indexes

DECLARE cIX CURSOR FOR     SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID         FROM Sys.Indexes SI             ...