Display the tenant name, and property address for all leases where the tenancy start date is between 1st January 2010 and 1st January 2015 inclusive

Question

SELECT LEASE_NO FROM LEASE WHERE STDATE >= 2010-01-01 AND ENDDATE <= 2015-01-01;

GAVE ME THIS ERROR ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Answer 1

Use DATE literals:

SELECT LEASE_NO
FROM   LEASE
WHERE  STDATE  >= DATE '2010-01-01'
AND    ENDDATE <= DATE '2015-01-01';

or use TO_DATE with string literals and an explicit format model:

SELECT LEASE_NO
FROM   LEASE
WHERE  STDATE  >= TO_DATE('2010-01-01', 'YYYY-MM-DD') 
AND    ENDDATE <= TO_DATE('2015-01-01', 'YYYY-MM-DD');

---

Your code does not work as 2010-01-01 is parsed as the number 2010 and then subtract the number 01 (which simplifies to 1) and then subtract another number 01 (which, again, simplifies to 1) so your SQL statement is effectively:

SELECT LEASE_NO
FROM   LEASE
WHERE  STDATE >= 2008
AND    ENDDATE <= 2013;

You cannot compare a date to a number which is why you get the error:

ORA-00932: inconsistent datatypes: expected DATE got NUMBER

---

Note:

Do not just "Put single quotes round your date values." as this would change them to strings and not to dates and then you would be relying on an implicit string-to-date conversion that will depend on the NLS_DATE_FORMAT session parameter and you are likely (with the default NLS settings) to get an error.

For example:

SELECT LEASE_NO
FROM   LEASE
WHERE  STDATE >= '2010-01-01'
AND    ENDDATE <= '2015-01-01';

With the default NLS_DATE_FORMAT of DD-MON-RR, gives the error:

ORA-01861: literal does not match format string

db<>fiddle here