Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

In a database, I am trying to pull information that is later than a specified date. I should note beforehand that the date is in an odd format: YYYYMMDDHH24MISS## where ## is a two letter string which defines something useless to my query. Thus, I am using substr to just remove them.

My query, below, throws the following error, and I canot find out why:

[Error Code: 920, SQL State: 42000] ORA-00920: invalid relational operator

My Query:

SELECT *
  FROM table_name
 WHERE to_date(substr(COLUMN_NAME,1,14), 'YYYYMMDDHH24MISS')) >=
       to_date('MIN_DATE', 'YYYYMMDDHH24MISS')
I have checked to make sure the dates are being defined correctly, and they are.
Example of what I have used for MIN_DATE is: 20140101000000
                Oh my god.. I have been stuck on this issue for half an hour.... Thanks so much @JoachimIsaksson. Isn't there a different error for too many parenthesis, though??
– Ryan_W4588
                Jul 25, 2014 at 18:01
                I agree that there should be, but sadly Oracle's error messages aren't known for always being straight forward.
– Joachim Isaksson
                Jul 25, 2014 at 18:02
                Odd, I didn't even look at the parenthesis. Well, from now on that will be the first thing I check.
– Ryan_W4588
                Jul 25, 2014 at 18:03
You get this error in Oracle when you are missing a comparison operation, such as = -- as John Maillet already noted.
My concern is the second part of the where clause:
where to_date(substr(COLUMN_NAME, 1, 14), 'YYYYMMDDHH24MISS') >=
                  to_date('MIN_DATE', 'YYYYMMDDHH24MISS')
You have MIN_DATE in single quotes.  This is interpreted as a string with eight letters in it, starting with 'M' and ending with 'E'.  This is not interpreted as a variable.  Presumably you mean:
where to_date(substr(COLUMN_NAME, 1, 14), 'YYYYMMDDHH24MISS') >=
                  to_date(MIN_DATE, 'YYYYMMDDHH24MISS')
You should only use single quotes for string and date constants.
I should add that you should be able to do this comparison without having to convert to dates:
where left(COLUMN_NAME, 14) = MIN_DATE
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.