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
– plalx, Commented Jun 9, 2017 at 12:02
Explain what you are trying to do. Sample data and desired results are helpful. — Gordon Linoff
– Gordon Linoff, Commented Jun 9, 2017 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
– Covert, Commented Jun 9, 2017 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
– Whencesoever, Commented Jun 9, 2017 at 12:05
@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
– Whencesoever, Commented Jun 9, 2017 at 12:22

SqlZim · Accepted Answer · 2017-06-09 12:14:16Z

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

etsa · Accepted Answer · 2017-06-09 12:05:47Z

1

Can you try this:

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

answered Jun 9, 2017 at 12:05

etsa

5,0501 gold badge9 silver badges18 bronze badges

Collectives™ on Stack Overflow

Why aren't sql dates getting compared?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related