Where conditions. != in two fields

Question

This query gives me exactly what i want, BUT it's not nice ;)

select *
from sel, (
    select * from sel where ttype = 15) as dl15
where ttype = 16
and sel.code = dl15.code
and convert(varchar,dl15.vdate,0)+convert(varchar, dl15.vtime,0) !=
    convert(varchar,sel.vdate,0)+convert(varchar, sel.vtime,0)

maybe someone can help me to convert this conditions convert(varchar,dl15.vdate,0)+convert(varchar, dl15.vtime,0) != convert(varchar,sel.vdate,0)+convert(varchar, sel.vtime,0) to more correct form

subquery result:

id          code    vdate               vtime
2000983484  3374347 Feb  1 2020 12:00AM Dec 30 1899  7:05PM

result without last conditions

id          code    vdate               vtime
2000983885  3374347 Feb  6 2020 12:00AM Dec 30 1899  8:00AM
2000983485  3374347 Feb  1 2020 12:00AM Dec 30 1899  7:43PM
2000952328  3374347 Feb  1 2020 12:00AM Dec 30 1899  7:05PM

needed result

id          code    vdate               vtime
2000983885  3374347 Feb  6 2020 12:00AM Dec 30 1899  8:00AM
2000983485  3374347 Feb  1 2020 12:00AM Dec 30 1899  7:43PM

Answer 1

Assumptions:

• vdate/vtime are of type datetime and include (milli/micro)seconds that are to be ignored (hence the convert(...,0))
• all columns are wanted from both tables

One query idea:

select s15.*,s16.*
from   sel s15
join   sel s16
on     s15.code  = s16.code
and    s15.ttype = 15
and    s16.ttype = 16
where (   convert(varchar,s15.vdate,0) != convert(varchar,s16.vdate,0)
       or convert(varchar,s15.vtime,0) != convert(varchar,s16.vtime,0)
      )

A variation on the where clause since smalldatetime is accurate to the minute (ie, smalldatetime has no seconds):

where (   convert(smalldatetime,s15.vdate) != convert(smalldatetime,s16.vdate)
       or convert(smalldatetime,s15.vtime) != convert(smalldatetime,s16.vtime)
      )

And if vdate/vtime are of type smalldatetime then there should be no need to call convert():

where (   s15.vdate != s16.vdate
       or s15.vtime != s16.vtime
      )