t-sql function to determine someone category

Question

If I am given a date like 1999-07-08 15:49:00 what would be a good function to determine whether is an AM shift, PM shift or a NOC shift?

--AM: 06:45:00 - 14:44:59
--PM: 14:45:00 - 22:59:59
--NOC: 23:00:00 - 06:44:59

Here is my attempt but then I noticed a bug

ALTER FUNCTION [dbo].[DateToNocShift]
(
    -- Add the parameters for the function here
    @DummyDate DATETIME
)
RETURNS VARCHAR(10)
AS
BEGIN
    -- Declare the return variable here
        DECLARE @Shift VARCHAR(10)


        DECLARE @DateValues TABLE
        (
            RawDate DATETIME, 
            HourNow int,
            MinuteNow int,
            TimeHourMinute FLOAT,
            Shift VARCHAR(4)
        )
        INSERT INTO @DateValues
        VALUES
        (
            @DummyDate,
            DATEPART(hour,@DummyDate),
            cast(DATEPART(minute,@DummyDate)as decimal),
            ROUND(DATEPART(hour,@DummyDate) + cast(DATEPART(minute,@DummyDate)as decimal)/60,2),
            null
        )



        UPDATE @DateValues 
        SET Shift = 'AM'
        WHERE TimeHourMinute BETWEEN 6.75 AND 14.74 -- good estimate

        UPDATE @DateValues 
        SET Shift = 'PM'
        WHERE TimeHourMinute BETWEEN 14.75 AND 22.99

        UPDATE @DateValues 
        SET Shift = 'NOC'
        WHERE TimeHourMinute BETWEEN 23.00 AND 6.74

        SELECT @Shift = Shift FROM @DateValues


        RETURN @Shift

Answer 1

Another approach would be to create a table with a row in it for every minute of the day, 1440 rows.

CREATE TABLE ShiftCheck
(
id int,
MyTime datetime  not null,        
ShiftNumber int not null,
ShiftName char(3) not null      
CONSTRAINT [PK_ShiftCheck] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)
)    

populate that table with a statement similar to this

; WITH DateIntervalsCTE AS
(
SELECT 0 i, cast('1/1/1900' as datetime)  AS Date
UNION ALL
SELECT i + 1, DATEADD(MINUTE, i,cast('1/1/1900' as datetime)  )
FROM DateIntervalsCTE 
WHERE DATEADD(MINUTE, i, cast('1/1/1900' as datetime) ) < cast('1/2/1900' as datetime) 
)
SELECT 
ROW_NUMBER() over (order by Date),
Date,
CASE 
    WHEN convert(varchar, Date, 108) >= '06:45:00' and convert(varchar, Date, 108) < '14:45:00' then 1
    WHEN convert(varchar, Date, 108) >= '14:45:00' and convert(varchar, Date, 108) < '23:00:00' then 2
    ELSE 3 
END,    
CASE 
    WHEN convert(varchar, Date, 108) >= '06:45:00' and convert(varchar, Date, 108) < '14:45:00' then 'AM'
    WHEN convert(varchar, Date, 108) >= '14:45:00' and convert(varchar, Date, 108) < '23:00:00' then 'PM'
    ELSE 'NOC' 
END
 FROM DateIntervalsCTE
 OPTION (MAXRECURSION 32767);

Then, you just need to join to the ShiftCheck table on a time. There is only a need to calculate what time a shift is in one time, to populate the table.

Scalar Valued Functions, like the one listed in your question are executed for every row in a given query. For example

Select *,[dbo].[DateToNocShift](ShiftDate)
from myTable

the function will be executed for every row in myTable which at a certain point (Saturday morning while you are sleeping) will get very slow. In conclusion, this will eventually become a performance problem and eventually someone will want to see the word 1st, 2nd, 3rd for the shift name. This solution will solve both of those as well as force a look-up instead of a calculation.

*If that is not accurate enough, put a row for every second of the day (24*60*60 = 86400 rows, still not that big for sql server) *I took the generate sql from here

Answer 2

You don't need so much code, a single CASE statement will do

CREATE FUNCTION [dbo].[DateToNocShift]
(
    -- Add the parameters for the function here
    @DummyDate DATETIME
)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @dateChar varchar(8)
set @dateChar = convert(varchar, @DummyDate, 108)
RETURN CASE
    WHEN @dateChar >= '06:45:00' and @dateChar < '14:45:00' then 'AM'
    WHEN @dateChar >= '14:45:00' and @dateChar < '23:00:00' then 'PM'
    ELSE 'NOC'
    END -- CASE
END