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?

Row generator subquery is OK, it works just fine if parameters do follow format model you specified. Are you sure that it (and not some other part of query) returns that error? — Littlefoot
– Littlefoot, Commented Jan 19, 2023 at 18:31
Pass dates as dates, not as strings. Then you will not need to_date — astentx
– astentx, Commented Jan 19, 2023 at 19:00
parameters would be something like this : P_TO_DATE (2020-01-01T00:00:00.000+00:00) P_FROM_DATE (2021-01-01T00:00:00.000+00:00), they are already dates, i use to_date to format them, i can use to_char to or i can even use the parameters as is, but nothing works — Aasem Shoshari
– Aasem Shoshari, Commented Jan 19, 2023 at 19:09
if i use to_char it says invalid number, if i use parameters as is it says invalid data type for datetime/interval arithmetic — Aasem Shoshari
– Aasem Shoshari, Commented Jan 19, 2023 at 19:12

d r · Accepted Answer · 2023-01-19 19:27:46Z

0

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

edited Jan 19, 2023 at 19:27

answered Jan 19, 2023 at 18:37

d r

8,6622 gold badges9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Aasem Shoshari Over a year ago

i used it as separate query, doesn't work, same error

d r Over a year ago

Can you catch the values that you are getting to be bound? This error is usualy missmatch of string with the specified format. Even quotes could cause the problem.

Aasem Shoshari Over a year ago

parameters would be something like this : P_TO_DATE (2020-01-01T00:00:00.000+00:00) P_FROM_DATE (2021-01-01T00:00:00.000+00:00), they are already dates, i use to_date to format them, i can use to_char to or i can even use the parameters as is, but nothing works

d r Over a year ago

You don't need anything to generate the dates. Just if you are selecting the date and want it shown in a particular format other than the one already set with NLS vars then you do To_Char to do the format you want. So, if you are gettingg dates then don't use to_date.

Aasem Shoshari Over a year ago

if i use to_char it says invalid number, if i use parameters as is it says invalid data type for datetime/interval arithmetic

|

Collectives™ on Stack Overflow

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

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related