sql returns dates less then asked for

[jeffc...@gmail.com]

sql returns dates less then asked for

I have a query that is supposed to return records greater than a
certain date, but it is returning records that are way less than the
date in the query. I don't understand why. I have just found out that
if you remove the OR statment it works fine. I don't get it, please
shed some light here. Here is the query.
Thanks.

select
dbwonum, dbopendate,dbstorenum
from workorder
where dbtype = 201
or dbtype = 236
and dbopendate >= '1/1/2004'
order by dbopendate asc;

Here is a describe on the table
DBWONUM NOT NULL NUMBER(11)
DBSTORENUM NOT NULL NUMBER(4)
DBSUMMARY VARCHAR2(255)
DBTYPE NOT NULL NUMBER(11)
DBASSID NUMBER(6)
DBRECEIVEDBY NOT NULL NUMBER(7)
DBRESPONSIBLE NOT NULL NUMBER(7)
DBSTATUS NOT NULL NUMBER(3)
DBFOLLOWTYPE NOT NULL NUMBER(2)
DBOPENDATE NOT NULL DATE
DBDUEDATE NOT NULL DATE
DBCLOSEDATE DATE
DBDESKDESC CLOB
DBDESKRESLTN CLOB
DBTECHDESC CLOB
DBTECHRESLTN CLOB
DBREPEATCALL NOT NULL NUMBER(1)
DBFOLLOWDATE DATE
DBNOTES VARCHAR2(20)
DBSUPERID NUMBER(6)
DBLASTMOD VARCHAR2(25)
DBAFTERHOUR NOT NULL NUMBER(1)

[Eric de Redelijkheid]

Anno Domini 18-3-2005 17:23, jeffc...@gmail.com sprak aldus:

>sql returns dates less then asked for
>
>I have a query that is supposed to return records greater than a
>certain date, but it is returning records that are way less than the
>date in the query. I don't understand why. I have just found out that
>if you remove the OR statment it works fine. I don't get it, please
>shed some light here. Here is the query.
>Thanks.
>
>select
>dbwonum, dbopendate,dbstorenum
>from workorder
>where dbtype = 201
>or dbtype = 236
>and dbopendate >= '1/1/2004'
>order by dbopendate asc;
>
>

It might be a conversion problem

try

select
dbwonum, dbopendate,dbstorenum
from workorder

where dbtype in (201,236)
and dbopendate >=to_date('01-JAN-2004','DD-MM-YYYY')
order by dbopendate asc;

It would depend on what you mean: do you want rows where dbtype = 201 and 236 where the date condition applies to both dbtype values or does the date condition apply only to dbtype 236. In that case I think you should use brackets in your where clause.

for example:

select
dbwonum, dbopendate,dbstorenum
from workorder
where dbtype = 201

or (dbtype=236 and dbopendate >=to_date('01-JAN-2004','DD-MM-YYYY'))

[jeffc...@gmail.com]

I got it figured out. Thanks. I did not have the ( ) in the correct
places.