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
2 Answers
You have an extra parenthesis at the end of the first to_date
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
