Append time from table to getdate() while using case

Question

I am trying to append time from a column to a date and use in a case statment

getdate() at time zone 'Central Standard Time'

this is what i've tried so far

select case when 
getdate() at time zone 'Central Standard Time'  <= 
cast(cast (getdate() at time zone 'Central Standard Time'as date) as datetime) + endtime from tablename where id = 'uniqueid'
1 else 0 end

datatype of endtime column is nvarchar and the format of the time is 21:00

and this is the error that i am getting

Incorrect syntax near the keyword 'from'

I have tried using this solution as well https://stackoverflow.com/a/700647 but still the same error. But when i do

select CAST(CAST(getdate() at time zone 'Central Standard Time' AS DATE) AS DATETIME) + endtime from tablename where id = 'uniqueid'

i get a proper response of 2020-01-07 07:00:00.000

and this seems to work fine as well

select case when
getdate() at time zone 'Central Standard Time' >= 
CAST(CAST(CAST(getdate() at time zone 'Central Standard Time'AS DATE) AS DATETIME) + '' + '10:00'as DATETIME)
then 1 else 0
end

returns 1

but

select case when 
getdate() at time zone 'Central Standard Time' <=
cast(CAST(CAST(getdate() at time zone 'Central Standard Time' AS DATE) AS DATETIME) + endtime from table where id = 'uniqueid' as datetime) 
then 1 else 0 end

returns Incorrect syntax near the keyword 'where'

I am not sure if i am doing something wrong or is it a limitation.

score 2 · Accepted Answer · answered Jan 07 '20 at 17:45

The big issue is that you are trying to use a subquery without using a subquery, which is what the syntax error is all about. Try this instead (expanded out for clarity)

SELECT 
    CASE 
        WHEN getdate() at TIME zone 'Central Standard Time' <= 
            (SELECT cast(cast(getdate() at TIME zone 'Central Standard Time' AS DATE) AS DATETIME) + endtime FROM tablename WHERE id = 'uniqueid') -- Subquery
            THEN 1 
        ELSE 0 
    END

Append time from table to getdate() while using case

1 Answers1