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.

You shouldn't have to explicitly convert dates to compare them to strings btw. — plalx, Jun 09 '17 at 12:02
Explain what you are trying to do. Sample data and desired results are helpful. — Gordon Linoff, Jun 09 '17 at 12:03
@GordonLinoff nothing special. Just what I posted is what I am trying to do, comparing ExpiryDate and time with current, if it's less than current datetime then it shouldn't be displayed else displayed — Covert, Jun 09 '17 at 12:04
it's not less in values. IT"S MORE. 17:50 is after 16:50. That's why it returns data. — Whencesoever, Jun 09 '17 at 12:05
Can u share simple query without table column reference with dummy data — Kaushik Thanki, Jun 09 '17 at 12:12
@Covert 2017-06-08 17:50:54.000 >= 2017-06-08 16:50:54.000 that's why that is true. I don't know what You don't understand — Whencesoever, Jun 09 '17 at 12:22

score 1 · Answer 1 · answered Jun 09 '17 at 12:05

1

Can you try this:

convert(datetime,convert(varchar,w.ExpiryDate,121) +  ' ' + w.ExpiryTime, 121 )

answered Jun 09 '17 at 12:05

etsa

5,020
1
7
18

score 1 · Accepted Answer · answered Jun 09 '17 at 12:14

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

Why aren't sql dates getting compared?

2 Answers2