Why aren't sql dates getting compared?

Question

I am comparing dates and times but it doesn't work properly sometimes

Code:

Select nit_no, workno, convert(datetime,convert(varchar,w.ExpiryDate,106) +  ' ' + w.ExpiryTime), getdate()
from works w
where NIT_No= 3594 and WorkNo=1
and convert(datetime,convert(varchar,w.ExpiryDate,106) +  ' ' + w.ExpiryTime) <= getdate()

Values:

convert(datetime,convert(varchar,w.ExpiryDate,106) +  ' ' + w.ExpiryTime)= 2017-06-08 16:50:54.000  
getdate()= 2017-06-08 17:50:54.000

ExpiryDate is of DATE type and ExpiryTime is of type VARCHAR

It doesn't work properly, getdate() is less than another expression and stills returns the data.

Update: What I am trying to do is comparing ExpiryDate and ExpiryTime with current Datetime i.e. Getadate(), if expiry date and time is less than current datetime then it shouldn't be displayed else displayed.

Answer 1

Instead of converting to characters and converting back, just convert the expirytime to a time data type, and add that time to a expirydate that is converted to datetime like so:

select *
  , expirydatetime = dateadd(millisecond
        ,datediff(millisecond,0,convert(time(7),w.expirytime))
        ,convert(datetime,w.expirydate)
      )
from works w
where dateadd(millisecond
        ,datediff(millisecond,0,convert(time(7),w.expirytime))
        ,convert(datetime,w.expirydate)
      ) < getdate()

or for use with datetime2

select *
  , expirydatetime2 = dateadd(millisecond
        ,datediff(millisecond,0,convert(time(7),w.expirytime))
        ,convert(datetime2(7),w.expirydate)
      )
from works w
where dateadd(millisecond
        ,datediff(millisecond,0,convert(time(7),w.expirytime))
        ,convert(datetime2(7),w.expirydate)
      ) < sysdatetime()

rextester demo: http://rextester.com/LDY81881