A test with datetime split into two columns

sql

Drop table if exists DateTimeTable
Drop table if exists DateTableTime
Create table DateTimeTable (id int identity(1,1) primary key, mydatetime datetime)
Create table DateTableTime (id int identity(1,1) primary key, mydate date, mytime time)

--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 
Where n between 1 and 100),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

Insert into DateTableTime(mydate,mytime)

select  Cast(dateadd(minute,n, getdate()) as date) dt, 
Cast(dateadd(minute,n, getdate()) as time) tm  from Nums

Insert into DateTableTime(mydate,mytime)
select mydate,mytime  from DateTableTime

Insert into DateTableTime(mydate,mytime)
select mydate,mytime  from DateTableTime

Insert into DateTableTime(mydate,mytime)
select mydate,mytime  from DateTableTime

Truncate table [dbo].[DateTimeTable]

insert [dbo].[DateTimeTable]

select try_Cast(concat(mydate ,'T' ,  mytime) as datetime2 ) as MyDateTime from DateTableTime



--Check queries 
Set statistics io,time on

SELECT
    SUM(CASE WHEN MyTime >= '09:00:00' AND MyTime < '17:00:00' THEN 1 ELSE 0 END) AS Shift1_Count
    ,SUM(CASE WHEN MyTime >= '17:00:00' OR MyTime < '01:00:00' THEN 1 ELSE 0 END) AS Shift2_Count
    ,SUM(CASE WHEN MyTime >= '01:00:00' AND MyTime < '09:00:00' THEN 1 ELSE 0 END) AS Shift3_Count
FROM DateTableTime
WHERE MyDate >= '4/5/2022' AND MyDate < '4/6/2022';


SELECT
    SUM(CASE WHEN Cast(MyDateTime as time) >= '09:00:00' AND Cast(MyDateTime as time) < '17:00:00' THEN 1 ELSE 0 END) AS Shift1_Count
    ,SUM(CASE WHEN Cast(MyDateTime as time) >= '17:00:00' OR Cast(MyDateTime as time) < '01:00:00' THEN 1 ELSE 0 END) AS Shift2_Count
    ,SUM(CASE WHEN Cast(MyDateTime as time) >= '01:00:00' AND Cast(MyDateTime as time) < '09:00:00' THEN 1 ELSE 0 END) AS Shift3_Count
FROM [dbo].[DateTimeTable] 
WHERE MyDateTime >=  '4/5/2022' AND MyDateTime < '4/6/2022';


Set statistics io,time off