ERROR at line 3: ORA-00911: invalid character" when joining tables

Question

I have a working PAINTER and PAINTING table which both work correctly. I am trying to Join them together where the PAINTING.PAINTER_Name (Artist who painted the painting) = PAINTER.AName (Artist Name). The only constraint is that the artist has to be born in the 19th century (Check WHERE Statement).
I have ran this code in I get an error reading "ERROR at line 3: ORA-00911: invalid character WHERE ABDate BETWEEN date’1800-01-01’ AND date’1899-12-29’".
I have attached the table schema and the query i am trying to tun. Any tips?
CREATE TABLE PAINTER    
(AName varchar2 (15),    
ABDate date,    
ADdate date,    
ACountry varchar2 (15),    
constraint pkPAINTER PRIMARY KEY(AName));

CREATE TABLE PAINTING    
(Painting_Name varchar2(15),    
Year_Painted varchar2(15),    
Est_Value varchar2(15),    
Museum_Name varchar2(15),    
PAINTER_Name varchar2(15),
constraint pkPainting_Name PRIMARY KEY (Painting_Name),    
constraint fkPAINTING1 foreign key (PAINTER_Name) references PAINTER,    
constraint fkPAINTING2 foreign key (Museum_Name) references MUSEUM);

SELECT PAINTING.Year_Painted, PAINTING.Painting_Name, PAINTER.ABDate
FROM PAINTING JOIN PAINTER ON PAINTING.PAINTER_Name = PAINTER.AName
WHERE ABDate BETWEEN '1800/01/01' AND '1899/12/31'
ORDER BY Painting_Name;

Gordon Linoff · Accepted Answer

Try using a date literal:
SELECT p.Year_Painted, p.Painting_Name, pr.ABDate
FROM PAINTING p JOIN
     PAINTER pr
     ON p.PAINTER_Name = p.AName
WHERE pr.ABDate BETWEEN DATE '1800-01-01' AND DATE '1899-12-31'
ORDER BY Painting_Name;

EDIT:
Based on your error message:

ERROR at line 3: ORA-00911: invalid character WHERE ABDate BETWEEN date’1800-01-01’ AND date’1899-12-29’".

You are using "fancy" or "smart" single quotes for the dates.  The proper delimiter for the date string is a single quote, and it should look like ', not ’.

Popeye · Answer

You should use to_date or date literal to create date from string as follows:
WHERE ABDate BETWEEN DATE '1800-01-01' AND DATE '1899-12-31'

OR
WHERE ABDate BETWEEN TO_DATE('1800/01/01','YYYY/MM/DD') 
                 AND TO_DATE('1899/12/31','YYYY/MM/DD')

-- Update
You have used wrong quotes (tilted quotes - ’). You need to use normal single quotes (')
date’1800-01-01’ -- opening and closing quotes are not normal single quotes

Use
date'1800-01-01'

ERROR at line 3: ORA-00911: invalid character" when joining tables

2 Answers

Add your own answers!

Ask a Question