BETWEEN DATES (TIME) HELP

Hi,

I need to display the employees from a current work shift. These are the following work shift:

4 AM – 12 PM

12 PM – 8 PM

8 PM – 4 AM

I’m having problems in third one “8 PM – 4 AM”. The next code is the one that I’m working on. If somebody knows a better way to do this let me know or if somebody can help me with this go ahead.

begin

declare @.from_time varchar(25);

set @.from_time = '8:00:00.000 PM';

declare @.to_time varchar(25);

set @.to_time = '4:00:00.000 AM';

declare @.current_date VARCHAR(25);

set @.current_date = getdate();

--select convert(smalldatetime, substring(@.current_date, 0, 13) + @.from_time),

--convert(smalldatetime, substring(@.current_date, 0, 13) + @.to_time),

--getdate();

if getdate() BETWEEN convert(smalldatetime, substring(@.current_date, 0, 13) + @.from_time) AND

convert(smalldatetime, substring(@.current_date, 0, 13) + @.to_time)

print 'OK';

else

print 'NO';

end

That's because of midnight crossover - the dates are changed also. Try this, it's far more simple and reliable:

Code Snippet

declare @.From int, @.To int, @.DT int

select @.From = 20, @.To = 4, @.DT = datepart(hh, getdate())

if @.DT >= @.From or @.DT < @.To
print 'OK'
else
print 'No'

|||Yeah. Thanks.