After a specific time of day - which method is better?

Question

I need to know (and label) whether a particular event in a subquery happens after 3:30 PM (regardless of the date).
With my setup, I haven't been able to determine by trial which is more efficient (they come back too quickly) and the execution plan isn't helpful.

The second (#2) seems more efficient to me because the literal only needs to be cast once (leaving one operation per row).
However, sometimes operations like this are deceiving, so I was hoping that someone could tell me which is better.

CREATE TABLE #jccTestData([ProcessDate] datetime);
INSERT INTO #jccTestData ([ProcessDate])
VALUES 
    ('2019-03-12 10:23:28.000') ,('2019-03-17 11:22:40.000'), ('2019-03-18 11:25:30.000')
    ,('2019-03-19 11:42:02.000') ,('2019-03-11 12:45:30.000') ,('2019-03-12 13:14:20.000')
    ,('2019-03-13 15:20:13.000') ,('2019-03-14 15:29:40.000') ,('2019-03-15 15:29:59.997') 
    ,('2019-03-16 15:30:00.000') ,('2019-03-17 15:30:00.003') ,('2019-03-18 16:25:30.000')
    ,('2019-03-12 23:59:59.997') ,('2019-03-13 00:00:00.003') ,('2019-03-14 00:00:00.000')
    ,('2019-03-15 03:14:20.000') ,('2019-03-16 05:20:13.000')

SELECT 
    [ProcessDate]
    , Case When datepart(HH, [ProcessDate]) > 15
        OR (datepart(HH, [ProcessDate]) = 15 And datepart(n, [ProcessDate]) >= 30) 
        Then 'After 3:30 PM' 
        End As [After 3:30? #1]
    , Case When cast([ProcessDate] As time) >= cast('15:30:00' As time) 
        Then 'After 3:30 PM' 
        End As [After 3:30? #2]
FROM #jccTestData  
ORDER BY [ProcessDate]

DROP TABLE #jccTestData

The second one also seems more maintainable.

If there's a third option I'm open to that also.
SQL Server 2012+

Answer 1

Personally, if you can, I would actually add a computed column to your table. CONVERT/CAST to the time data type is definitely the way to go if using that route, but if you can add a persisted computed column then you can index that column, or add it to your existing ones, and maintain SARGability if you ever need to query this data in the WHERE (I realise the sample here is a temporary table, but i doubt the real scenario is):

ALTER TABLE #jccTestData ADD ProcessTime AS CONVERT(time,ProcessDate) PERSISTED;

Then you can just query that new column:

SELECT [ProcessDate],
       CASE WHEN ProcessTime >= '03:30' THEN 'After 3:30 PM' END AS [After 3:30?]
FROM #jccTestData
ORDER BY [ProcessDate];

Also, avoid using ordinal numbers for columns in the ORDER BY, you are far better off using the columns actual name.

Side note, you don't need to have a column for casting/converting to the date datatype. CONVERT(date,ProcessDate) and CAST(ProcessDate AS date) are both SARGable.

Answer 2

Since you are asking about efficiency, I did some benchmarks on more than a million real records. The last method (CAST) as below reports more CPU time. All times were in milliseconds, so you would need millions of records to notice any difference.

IIF((DATEPART(HH, dateStamp) * 60) + DATEPART(MINUTE, dateStamp) >= 930, 'After 3:30 PM', '')
IIF(DATEPART(HH, dateStamp) > 15 OR (DATEPART(HH, dateStamp) = 15 AND DATEPART(N, dateStamp) >= 30), 'After 3:30 PM', '')
IIF(CAST(dateStamp AS TIME) >= CAST('15:30:00' AS TIME), 'After 3:30 PM', '')

1) Average CPU time: 477; Average Elapsed time: 242

2) Average CPU time: 469; Average Elapsed time: 236

3) Average CPU time: 808; Average Elapsed time: 413