SQL statement not working in Oracle Database-CodePudding

SELECT t.* from test INNER JOIN apple a on a.id = t.id
WHERE t.type IN (12,13) AND t.file= '1234'
AND TO_DATE(t.Date,'YYYY/MM/DD')>=TO_DATE(t.Date,'2020/01/01')

Error is "Date Format not Recognised"

CodePudding user response：

The problems I can see immediately are as follows:

in your second to_date, '2020/01/01' is an actual date; Oracle to_date requires a date format in that location. I'm understanding you want to include rows with t.Date value since start of 1st January 2020, in which case change the to_date command to read as follows:

to_date('2020/01/01','YYYY/MM/DD')

you reference t in your query but it is not set up as an alias for "test". It should read:

SELECT t.* from test t

eg.

SELECT t.* 
from test t
     INNER JOIN apple a on a.id = t.id
WHERE t.type IN (12,13)
AND t.file = '1234'
AND TO_DATE(t.Date,'YYYY/MM/DD') >= to_date('2020/01/01','YYYY/MM/DD')

As an aside I would also recommend that if possible you change the data type of column t.date in your table test to the Oracle date type rather than a string as among other benefits date arithmetic becomes much easier.