Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE lease (lease_no, stdate, enddate) AS
SELECT LEVEL,
       ADD_MONTHS(DATE '2000-01-01', 12 * (level - 1)),
       ADD_MONTHS(DATE '2000-01-01', 12 * (level - 1))
FROM   DUAL
CONNECT BY LEVEL <= 25;
25 rows affected

SELECT LEASE_NO
FROM   LEASE
WHERE  STDATE >= 20010-01-01
AND    ENDDATE <= 2015-01-01;
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

SELECT LEASE_NO
FROM   LEASE
WHERE  STDATE >= 2008
AND    ENDDATE <= 2013;
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

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


LEASE_NO


11

12

13

14

15

16


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');


LEASE_NO


11

12

13

14

15

16


SELECT *
FROM   NLS_SESSION_PARAMETERS
WHERE  PARAMETER = 'NLS_DATE_FORMAT';


PARAMETER
VALUE


NLS_DATE_FORMAT
DD-MON-RR


SELECT LEASE_NO
FROM   LEASE
WHERE  STDATE >= '2010-01-01'
AND    ENDDATE <= '2015-01-01';
ORA-01861: literal does not match format string