Proper format for date in Oracle SQL

Question

I am trying to run this query and every time I try to fix it I get all sorts of different errors, I believe this to be the correct syntax but it keeps telling me I have an invalid date identifier. Ive looked online but can't quite find what I'm trying to do here.

SELECT CUST_FNAME, CUST_LNAME, CUST_STREET, CUST_CITY, CUST_STATE, CUST_ZIP
FROM LGCUSTOMER, LGPRODUCT, LGBRAND 
WHERE BRAND_NAME = 'FORESTERS BEST' 
AND INV_DATE BETWEEN '15-JUL-11' AND '31-JUL-11'
ORDER BY CUST_STATE, CUST_LNAME, CUST_FNAME;

Answer 1

In my version of Oracle I use '15 Jul 11' But this is setting dependent. To be certain you should use something like TO_DATE('20110715','YYYYMMDD') as it explicitly states the format.

Answer 2

It seems that data type of the column INV_DATE is DATE. If so, you should provide DATE value to the BETWEEN condition.

You can check the NLS_DATE_FORMAT parameter of your database. It specifies the default date format for TO_CHAR and TO_DATE function. IF the NLS_DATE_FORMAT is DD-MMM-YY or DD-MMM-RR, your query should run ok. Otherwise, you should change the date string in your query to follow the NLS_DATE_FORMAT. Or you can use TO_DATE function like the following.

...
AND INV_DATE BETWEEN TO_DATE('15-JUL-11', 'DD-MMM-RR') AND TO_DATE('31-JUL-11', 'DD-MMM-RR')
...

Perhaps you should want to read what NLS_DATE_FORMAT, check oracle documentation.

Answer 3

And besides, you should rarely use the date format 'MON', as it depends the language and environment as well.

For example, it will fail if you try to convert '26-NOV-13' to date using format DD-MON-RR in Chinese language environment. because the MON will be matched with "11月", not "NOV"

Answer 4

You can also use the standard format for date literals:

SELECT CUST_FNAME, CUST_LNAME, CUST_STREET, CUST_CITY, CUST_STATE, CUST_ZIP
FROM LGCUSTOMER, LGPRODUCT, LGBRAND 
WHERE BRAND_NAME = 'FORESTERS BEST' 
AND INV_DATE BETWEEN DATE '2011-07-15' AND DATE '2011-07-31'
ORDER BY CUST_STATE, CUST_LNAME, CUST_FNAME;