ORA-01843: not a valid month

Question

I am having an issue with my sql query.

  create table rental
  (     
  rental_id NUMBER(5) NOT NULL,
  rental_date timestamp NOT NULL, 
  inventory_id NUMBER(5) NOT NULL,
  customer_id NUMBER(5) NOT NULL,
  return_date timestamp NOT NULL,
  staff_id NUMBER(5) NOT NULL,
  constraint rental_primary PRIMARY KEY (rental_id),
  constraint rental_foreign FOREIGN KEY (inventory_id) REFERENCES 

  inventory(inventory_id),
  constraint rental_foreign2 FOREIGN KEY (customer_id) REFERENCES
  customer(customer_id),
  constraint rental_foreign3 FOREIGN KEY (staff_id) REFERENCES staff(staff_id),
  constraint rental_unique_rental_date unique (rental_date),
  constraint rental_unique_inventory_id unique (inventory_id),
  constraint rental_unique_customer_id unique (customer_id),
  constraint rental_rental_date check(to_char(rental_date,'YYYY/MM/DD HH:MI:SS AM') 
  between '2005/01/01 00:00:01 AM' AND '12/31/2015 11:59:59 PM'),
  constraint rental_return_date check(to_char(return_date,'YYYY/MM/DD HH:MI:SS AM') 
  between '2005/01/01 00:00:01 AM' AND '12/31/2015 11:59:59 PM')
  );

when I am trying to insert data, It is throwing this error "ORA-01843: not a valid month". Can anyone please suggest me what type of datatype can I use for rental_date and return date to get through this situation? Here is my sample data which I am trying to upload

    rental_date                   return_date
   5/24/2011  10:53:30 PM        5/26/2011  10:04:30 PM

Note : I can see this type of format in my excel sheet where I have my data but when I get error

       "ORA-01843: not a valid month    
     Row 145: 144,  2011-05-25 23:49:56  ,1689,357,  2011-06-01 21:41:56  ,2"

I am observing different format of time here.

Can anyone suggest me a solution?

Thanks in advance.

Answer 1

In your check constraints you specify TO_CHAR() with a date format mask of 'YYYY/MM/DD HH:MI:SS AM'. But the values you specify for the range are in two different formats, e.g.

'2005/01/01 00:00:01 AM' AND '12/31/2015 11:59:59 PM'

I think it would be a good idea to work with dates instead, as strings won't compare the way you think they do.

constraint rental_rental_date check(rental_date) 
    between to_date('2005/01/01 00:00:01 AM', 'YYYY/MM/DD HH:MI:SS AM') and
            to_date('2015/12/31 11:59:59 PM', 'YYYY/MM/DD HH:MI:SS AM')

However this may not be the root of your problem. It may be an conversion in your load process, which is why we need to know how you're loading the data.