Oracle SQL Connect By Level - literal does not match format string

Question

I want to generate dates between 2 dates coming from parameters in oracle fusion using the connect by level statement

SELECT   papf.person_number emp_id,
(SELECT to_date(:p_from_date,'dd-mm-yyyy') + level - 1 dt
from   dual
connect by level <= (
  to_date(:p_to_date,'dd-mm-yyyy') - to_date(:p_from_date,'dd-mm-yyyy') + 1
) ),
....

I get error ORA-01861: literal does not match format string, I tried to use to_char and change the format but doesn't work, the parameter date format is also dd-mm-yyyy, what is wrong here?

Answer 1

SELECT   
    dt --papf.person_number emp_id
FROM
    (
        SELECT to_date(:p_from_date,'dd-mm-yyyy') + level - 1 dt
        FROM   dual
        connect by level <= to_date(:p_to_date,'dd-mm-yyyy') - to_date(:p_from_date,'dd-mm-yyyy') + 1) papf   

Your inner query selects just the dt column, FROM was missing, brackets in wrong places... Anyway with variables bound as 01-01-2023 and 10-01-2023 the result is:

dt
----------
01-JAN-23
02-JAN-23
03-JAN-23
04-JAN-23
05-JAN-23
06-JAN-23
07-JAN-23
08-JAN-23
09-JAN-23
10-JAN-23

If your parameters are of DATE data type then

SELECT   
    To_Char(dt, 'dd-mm-yyyy') "DT"
FROM
    (
        SELECT :p_from_date + level - 1 dt
        FROM   dual
        connect by level <= :p_to_date - :p_from_date + 1) papf  

DT
----------
01-01-2023
02-01-2023
03-01-2023
04-01-2023
05-01-2023
06-01-2023
07-01-2023
08-01-2023
09-01-2023
10-01-2023