CODEWITHSUNDEEP
sqloracle-database
Matt Johnson
Matt Johnson

Reputation: 73

SQL Select Query fails when trying to show results in current month

I'm very new to SQL and learning. Currently I am working with a Select statement to only show orders from the current month. I am using 

SELECT
  ORDERS.ORDERID,
  ORDERS.CUSTOMERID,
  ORDERS.ORDERDATE,
  ORDERS.SHIPDATE
FROM
  ORDERS
WHERE Date >= '01-JAN-16' and Date <= '31-JAN-16'

But that fails with Invalid Relational Operator

Where am I going wrong?

Thanks for any help!

Upvotes: 0

Views: 46

Answers (4)

dnoeth
dnoeth

Reputation: 60462

You need to specify valid date literals:

SELECT
  ORDERS.ORDERID,
  ORDERS.CUSTOMERID,
  ORDERS.ORDERDATE,
  ORDERS.SHIPDATE
FROM
  ORDERS
WHERE ORDERS.ORDERDATE BETWEEN DATE '2016-01-01' and DATE '2016-01-31'

Or you calculate the date range based on today:

SELECT
  ORDERS.ORDERID,
  ORDERS.CUSTOMERID,
  ORDERS.ORDERDATE,
  ORDERS.SHIPDATE
FROM
  ORDERS
WHERE ORDERS.ORDERDATE >= TRUNC(CURRENT_DATE, 'MON') 
  AND ORDERS.ORDERDATE < ADD_MONTHS(TRUNC(CURRENT_DATE, 'MON'), 1)

Upvotes: 0

Olivier
Olivier

Reputation: 128

DATE is a reserved word. You should use double quotes to escape reserved words.

Try WHERE "date" = ...

But i think you didn't use the correct colum name for your condition.

WHERE ORDERDATE= ...

You can also check your date format.

Upvotes: 0

LuvnJesus
LuvnJesus

Reputation: 631

If the field "Date" is really the name of your field, you will need to enclude it in tic marks as follows in your WHERE clause:

... WHERE `Date` >= '01-JAN-16' and `Date` <= '31-JAN-16'

Because Date is a reserved word. It looks like ORDERDATE and SHIPDATE are your field names ... should you be using one of those in your query? Also, be careful with your date format. By default, SQL uses YYYY-MM-DD but it depends on how you have your date fields setup. Here's an example using the ORDREDATE field in your table ...

SELECT ORDERS.ORDERID,ORDERS.CUSTOMERID,ORDERS.ORDERDATE,ORDERS.SHIPDATE FROM ORDERS WHERE date(ORDERDATE) >= "2016-01-01" and date(ORDERDATE) <= "2016-01-31";

Upvotes: 1

Khazratbek
Khazratbek

Reputation: 1656

It is caz' of your date format. Better way is the next query:

SELECT ORDERS.ORDERID,
       ORDERS.CUSTOMERID,
       ORDERS.ORDERDATE,
       ORDERS.SHIPDATE
  FROM ORDERS
 WHERE EXTRACT(month from date) = 1; --take records for january

Upvotes: 1

Related Questions