Time Increment data list in T-SQL

[ElEye]

T-SQL Script for a list of increment times

My goal was to create an efficient T-SQL script with the following criteria:
- create a list of incremented times (in minutes) of a 24-hour period
- one column for the actual value
- one column for a "friendly" display value
- one column to select the closest time to now (aka GETDATE)
- control appearance of result
- no defined functions
- no data table (fixed or temporary) required
- this written for SS2008

Code below. Here is an explanation:
- datetime datatype required to do math (DATEADD) so I chose '1900-01-01' as my initial date
- @TimeInc is the incrementing number of minutes
- @Selected creates a date time that is "now" for time except on the '1900-01-01' date
- @TimeRange establishes a range to select the nearest time to "now"
- Common Table Expression (CTE) used in lieu of a temp table
- within the CTE I used UNION ALL with DATEADD so it loops itself until the WHERE condition is met
- I converted the result set columns to time(0) to filter out the date ('1900-01-01') since all I wanted was the time
- I found that attempting to sort (ORDER BY) bogs down the server and was unnecessary since I wanted the time in natural order anyway
- I added MAXRECURSION in case my result set is greater than the SQL Server default of 100, figuring that 600 is plenty (15 min @TimeInc increment yields 97 results with midnight at the beginning and end)
- converting to varchar(5) (or could be LEFT 5) displays only hh:mm
- [oTime] is ideal as the value of an OPTION field in an HTML SELECT (24hr hh:mm)
- [tTime] is ideal as the display value in an HTML SELECT (12hr hh:mm:a/p)
- [tClosest] is ideal as the "selected" value in an HTML SELECT


CODE:

DECLARE @StTime datetime = '1900-01-01 00:00:00'
DECLARE @TimeInc int = (15)
DECLARE @Selected datetime = ('1900-01-01 ' + (CONVERT(varchar(2),(DATEPART(HH,GETDATE())))) + ':' + (CONVERT(varchar(2),(DATEPART(MI,GETDATE())))))
DECLARE @TimeRange int = (@TimeInc/2)
;
WITH [cte_Times]
([cTime])
AS
(
SELECT @StTime
UNION ALL
SELECT DATEADD(MINUTE,@TimeInc,[cTime]) FROM [cte_Times]
WHERE [cTime] BETWEEN @StTime AND '1900-01-01 23:59:59'
)

SELECT
(CONVERT(varchar(5),(CONVERT(time(0),[cTime])))) [oTime],
(
(CASE
WHEN ((DATEPART(HH,[cTime])) = 0) THEN '12'
WHEN ((DATEPART(HH,[cTime])) > 12) THEN (CONVERT(varchar(2),(DATEPART(HH,[cTime]) - 12)))
ELSE
(CONVERT(varchar(2),(DATEPART(HH,[cTime]))))
END)
+ ':'
+ (CASE WHEN ((DATEPART(MI,[cTime])) < 10) THEN '0' ELSE '' END)
+ (CONVERT(varchar(2),(DATEPART(MI,[cTime]))))
+ (CASE WHEN ((DATEPART(HH,[cTime])) < 12) THEN 'a' ELSE 'p' END)
) [tTime],
(CASE WHEN (DATEDIFF(MI,[cTime],@Selected) BETWEEN (@TimeRange * -1) AND @TimeRange) THEN ' selected' ELSE '' END) [tClosest]

FROM [cte_Times]
OPTION (MAXRECURSION 600)
GO


I welcome your critiques. Enjoy.

Dave
[

http://flashspot.com

]

 

[denimined]

Certainly seems to work, only the 0.00 appears at the beginning and end. Hence, if the current time was in that window, you would get 2 'selected' positions.

Coding note - if you put the code inside a pair of _code_ _/code_ tags, you get a nice code window that preserves spacing for you.

e.g.

SELECT (CONVERT(varchar(5), 
                (CONVERT(time(0), 
                         [cTime])))) as [oTime],
       ((CASE
           WHEN ((DATEPART(HH,[cTime])) = 0) THEN '12'
           WHEN ((DATEPART(HH,[cTime])) > 12) THEN (CONVERT(varchar(2),(DATEPART(HH,[cTime]) - 12)))
           ELSE (CONVERT(varchar(2),(DATEPART(HH,[cTime]))))
         END)
       + ':'
       + (CASE 
            WHEN ((DATEPART(MI,[cTime])) < 10) THEN '0' 
            ELSE '' 
          END)
       + (CONVERT(varchar(2),(DATEPART(MI,[cTime]))))
       + (CASE 
            WHEN ((DATEPART(HH,[cTime])) < 12) THEN 'a' 
            ELSE 'p' 
          END)
       ) as [tTime],
...

 

[ElEye]

Yes, my spacing could be improved.

Dave
[

http://flashspot.com

]