I have four related tables "suppliers", "products", "orderdetails", "orders" (I think that relationship should be made)

-"suppliers" has these columns

SUPPLIERID, COMPANYNAME, CONTACTNAME,
CONTACTTITLE, ADDRESS, CITY, REGION,
 POSTALCODE, COUNTRY, PHONE, FAX, HOMEPAGE

-"products" has these columns

PRODUCTID, PRODUCTNAME, SUPPLIERID,
 CATEGORYID, QUANTITYPERUNIT, UNITPRICE,
 UNITSINSTOCK, UNITSONORDER, REORDERLEVEL, DISCONTINUED

-"orderdetails" has these columns

ORDERID, PRODUCTID, UNITPRICE,
 QUANTITY, DISCOUNT

-"orders" has these columns

ORDERID, CUSTOMERID, EMPLOYEEID, 
TERRITORYID, ORDERDATE, REQUIREDDATE,
 SHIPPEDDATE, SHIPVIA, FREIGHT, SHIPNAME,
 SHIPADDRESS, SHIPCITY, SHIPREGION, SHIPPOSTALCODE,
 SHIPCOUNTRY

I need the names of the suppliers that served products the first two days of June 1997

I tried to do this but it doesn't work:

SELECT DISTINCT companyname FROM suppliers 
JOIN products ON suppliers.supplierid=products.supplierid
JOIN orderdetails ON products.productid=orderdetails.productid
JOIN orders ON orderdetails.orderid=orders.orderid

where  orderdate between TO_DATE('01/06/97', 'DD/MM/YY')
                    AND TO_DATE('02/06/97', 'DD/MM/YY');

CodePudding user response：

You have two problems:

1. You are using the format model YY which assumes that the date is in the current century so you are looking for dates in 2097 and not 1997. To fix it you can use the RR format model, a 4-digit year or use a date literal DATE '1997-06-01'.
2. In Oracle, a DATE is a binary data type that ALWAYS contains 7-bytes representing century, year-of-century, month, day, hour, minute and second; so it will always have a time component. You are searching from ??97-06-01 00:00:00 to ??97-06-02 00:00:00 and you will miss any data on the second day that does not have a midnight time component. You can search on the entire range or you can TRUNCate the time component to midnight.

To fix it you can use:

SELECT DISTINCT companyname
FROM   suppliers 
       JOIN products ON suppliers.supplierid=products.supplierid
       JOIN orderdetails ON products.productid=orderdetails.productid
       JOIN orders ON orderdetails.orderid=orders.orderid
WHERE  orderdate >= DATE '1997-06-01'
AND    orderdate <  DATE '1997-06-03';

or:

SELECT DISTINCT companyname
FROM   suppliers 
       JOIN products ON suppliers.supplierid=products.supplierid
       JOIN orderdetails ON products.productid=orderdetails.productid
       JOIN orders ON orderdetails.orderid=orders.orderid
WHERE  TRUNC(orderdate) BETWEEN TO_DATE('01/06/97', 'DD/MM/RR')
                            AND TO_DATE('02/06/97', 'DD/MM/RR');

Note: filtering on a date range allows Oracle to use an index on the orderdate column; if you use a function, such as TRUNC then Oracle will not use an index on the column and would require a separate function-based index.

CodePudding user response：

Use the following code it may help you.

Modified answer

I apologize for the confusion. In Oracle, you can use the TRUNC function to truncate the time part of the 'orderdate' field, like so:

SELECT DISTINCT companyname FROM suppliers 
JOIN products ON suppliers.supplierid=products.supplierid
JOIN orderdetails ON products.productid=orderdetails.productid
JOIN orders ON orderdetails.orderid=orders.orderid
WHERE TRUNC(orderdate) BETWEEN TO_DATE('1997-06-01','YYYY-MM-DD') AND  TO_DATE('1997-06-02','YYYY-MM-DD')

Or the following

SELECT DISTINCT companyname FROM suppliers 
JOIN products ON suppliers.supplierid=products.supplierid
JOIN orderdetails ON products.productid=orderdetails.productid
JOIN orders ON orderdetails.orderid=orders.orderid
WHERE orderdate >= TO_DATE('1997-06-01','YYYY-MM-DD') AND  orderdate < TO_DATE('1997-06-03','YYYY-MM-DD')

In this way you truncate the date to the day level and compare the date part of the field.