Display a string if getdate() has passed a certain time

Question

So this is what I have:

declare @breakend datetime,
        @breakstart datetime
set @breakend = (select Convert(varchar(20),DateAdd(month, 0, convert(varchar(20),getdate(), 102) + Space(1) + '9:00:00 AM'), 120))
set @breakstart = GETDATE()
select CONVERT(VARCHAR(20),DateAdd(SS,Datediff(ss,@breakstart, @breakend)%(60*60*24),0),108) as 'Hours until first break'

I was wondering how to print something like 'Past Break Time' if the time is passed 9:00AM?

So if its 8:00AM it will display '1 hour until break'. If it 9:01AM display 'Past Break Time'

Answer 1

You can just use case and between. If I understand you correctly this is how you can check if you date is between the break time.

declare @breakend datetime,
        @breakstart datetime
set @breakend = getdate() + 1
set @breakstart = GETDATE() - 1
select case when getdate() between @breakstart and @breakend then 1 else 0 end as 'result'

Answer 2

This is not exact number of hours but maybe this works

declare @breakend datetime,
    @breakstart datetime
set @breakend = (select cast(cast(getdate() as date) as varchar) + ' 09:00:00')
set @breakstart = getdate()

select
case 
    when @breakstart < @breakend then cast(datediff(hour,@breakstart,@breakend) as varchar) + ' hours until break'
    else 'Past break time'
end display

Answer 3

DECLARE @breakend datetime,
        @breakstart datetime
SET @breakend = (select Convert(varchar(20),DateAdd(month, 0, convert(varchar(20),getdate(), 102) + Space(1) + '09:00:00 AM'), 120))
SET @breakstart = GETDATE()

SELECT
    CASE WHEN @breakstart < @breakend THEN CAST(DATEDIFF(MINUTE,@breakstart,@breakend) as varchar(10)) + ' minutes left on break'
            ELSE 'Past Break Time' END AS Value