SQL statement throws “dpiStmt_execute: ORA-01821: date format not recognized”

I am doing an SQL exercise, where I have to determine the amount of returned films to the film store at the given date “2005.06.05” and the number of films, which were borrowed at this date. Somehow I always get the errow message standing in the title. Does someone know how, where I made the mistake and how to solve it ? Thanks 🙂

FROM ( 
   SELECT x_store_id, x_date, COUNT(x_date) AS num_return 
   FROM ( 
      SELECT x_store_id, x_date 
      FROM ( 
         SELECT TO_CHAR(rental.return_date, '2005.06.05') AS x_date, inventory.inventory_id, inventory.store_id AS x_store_id 
         FROM rental 
         INNER JOIN inventory ON rental.inventory_id = 
         inventory.inventory_id 
      ) 
      WHERE x_date LIKE '2005.06.05'
   ) 
   GROUP BY x_store_id, x_date 
)  
LEFT JOIN ( 
   SELECT y_store_id, COUNT(y_store_id) AS num_rental 
   FROM ( 
      SELECT y_store_id, y_date, x_date 
      FROM ( 
         SELECT rental.rental_date AS y_date, rental.return_date AS x_date, inventory.inventory_id, inventory.store_id AS y_store_id 
         FROM rental 
         INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id 
      ) 
      WHERE y_date <= TO_TIMESTAMP('2005.06.04', '2005.06.05') AND (x_date >= TO_TIMESTAMP('2005.06.06', '2005.06.05') OR x_date IS NULL) 
   ) 
   GROUP BY y_store_id 
) ON x_store_id = y_store_id 
ORDER BY num_return

Answer

Both to_char and to_timestamp receive data format as second parameter. And this is where the errors are

for example, this

TO_CHAR(rental.return_date, '2005.06.05')

is to be replaced presumably with that

TO_CHAR(rental.return_date, 'yyyy.mm.dd')

And same goes to the to_timestamp function you use in a WHERE part

Advertisement

Answer