CODEWITHSUNDEEP
sqloracle-database
amthomas2112
amthomas2112

Reputation: 47

SQL - Literal does not match format string

I apologize if this question has been asked in advance. I've been looking around for an answer to this, and all of the answers for this error message seem to involve date fields. However, there is no date field in the following:

create table tableName (
   trans_dest_name varchar2(50) not null,
   mmddyyyy        varchar2(8)  not null,
 constraint holiday_key primary key (trans_dest_name, mmddyyyy),
 constraint trans_dest_name_ch check(REGEXP_LIKE(trans_dest_name,'^[a-zA-Z0-9 ]{0,50}$')),
 constraint holiday_format check (
    (to_number(substr(mmddyyyy, 0, 2))) < 13
    AND (to_number(substr(mmddyyyy, 0, 2))) > 0
    AND (to_number(substr(mmddyyyy, 2, 2))) < 32
    AND (to_number(substr(mmddyyyy, 2, 2))) > 0
    AND (to_number(substr(mmddyyyy, 4, 2))) = 20)
);

insert into tableName VALUES ('Power Pass', '01012015');

FYI, this is a problem a coworker came to me with. I'm not exactly sure why they're using a string to represent a date, but this is the format they've decided to go with. The problem is...it seems as if the "Power Pass" value in the insert is throwing this particular error...not the date string.

SQL> SQL>  insert into tableName VALUES ('Power Pass', '01012015')
                                            *
ERROR at line 1:
ORA-01861: literal does not match format string

My original thought was that it WAS the date string that was causing the problem, perhaps in the attempt to convert it to a number in the constraints. It doesn't seem to be that way, as a new error message occurs when I had said coworker try inserting the date string as a number.

SQL> SQL> insert into tableName VALUES ('Power Pass', 01012015)
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

So this part is strange. Why is it expecting a DATE in that field? I'm boggled entirely by this problem. Any help would be greatly appreciated. Thanks!

Upvotes: 0

Views: 592

Answers (1)

Martin Drautzburg
Martin Drautzburg

Reputation: 5253

Except for the wrong indexing as pointed out by Maheswaran Ravisankar the code you posted does not throw an error. IOW: the error you see must come from something you didn't post. Here are some things you may try:

  • Check the original code. I assume you mildly edited it and the table wasn't really called "tableName". Maybe another error slipped in. Possibly you are not inserting into THIS table, but another one.
  • Lookup user_dependencies in the real DB and verify that really nothing, ex (i.e. no trigger etc.) depends on that table. Or use a tool like Toad to examine the table.
  • Lookup user_constraints to verify that there are no constraints which you didn't show us and which require a date conversion.
  • drop the table and insert again. You should get ORA-00942: table or view does not exist otherwise see 1st bullet.

Upvotes: 1

Related Questions