How to cast GETDATE() to be only time ignore day,month and year

Question

I have a table for store takt-time. It's compound with "day_shift(1)" and "night_shift(0)" like below.

If I put current time(by using GETDATE() function ). I need to know this current time is "day(1)" or "night(0)". my query like this.

SELECT [day_shift_flag]   
FROM  [company_working_time]
WHERE   GETDATE() BETWEEN [start_time] AND[end_time]

But it can't found because a date received from GETDATE() isn't '1900-01-01' How to compare a only time and ignore day,month,year. What should I do?

Answer 1

You can cast a DateTime as a Time data type. For example:

SELECT 
    [day_shift_flag]   
FROM  [company_working_time]
WHERE   
CAST(GETDATE() AS TIME) BETWEEN CAST([start_time] AS TIME) AND CAST([end_time] AS TIME)

Answer 2

By Following Way You can Ignore the day,month,year

       SELECT [day_shift_flag]   
        FROM  [company_working_time]
        WHERE   DatePart(HH,GETDATE()) BETWEEN DatePart(HH,[start_time]) AND DatePart(HH,[end_time])