php ORA-01861: literal does not match format string

Question

I'm writing a php script to insert data into an Oracle database, and I'm getting ORA-01861: literal does not match format string when I try to run it. It has something to do with the date and they way it's calculated, but I'm not sure how/where to fix it. In the table, the log_date is type date. Below is the section of code that I'm working with for the date after I've already established the db connection. Does it need to go in my $query definition?

$ticks = $mnemonic->timestamp . "\n";
$seconds = ($ticks - 621355968000000000) / 10000000;
$day = date("Y-m-d H:i:s", $seconds);

$query = "INSERT into TLM_Item(log_date) Values('$day')";

$updt = ociparse($conn, $query);
    if(!$updt){
    print "No query \n";
    exit;
    }

    $r = ociexecute($updt , OCI_COMMIT_ON_SUCCESS);

Gordon Linoff · Accepted Answer · 2014-10-06 17:42:59Z

1

Oracles default date format is not YYYY-MM-DD. Fortunately, though, Oracle supports the keyword DATE so support dates. I haven't used it in this context, but it should work:

$query = "INSERT into TLM_Item(log_date) Values (DATE '$day')";

Alternatively, you could use the built-in function to_date():

$query = "INSERT into TLM_Item(log_date) SELECT to_date('$day', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL";

edited Oct 6, 2014 at 17:42

answered Oct 6, 2014 at 16:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

4 Comments

fcr91 Over a year ago

The first option didn't work, but the second one did. Comments: 1) Typo: in the second option, "HH:MI:DD" should say "HH:MI:SS" 2) If you type it like as above you will get an error "ORA-01849: hour must be between 1 and 12". If you instead type "HH24:MI:SS" it will work.

fcr91 Over a year ago

Now, just a follow up question: if I want to insert other values in addition to the date, do I just use the whole expression "SELECT to_date('$day', 'YYYY-MM-DD HH:MI:DD') FROM DUAL" in a Values() expression along with the other values to be inserted?

fcr91 Over a year ago

Also, when I go to the DB to check the entries, the dates show up as: 10-JAN-14 with no time included. What is causing it to not appear?

Gordon Linoff Over a year ago

@fcr91 . . . The default format for printing out dates in Oracle is to leave out hte time (use to_char() if you want more details). If you want to insert more columns, use insert . . . select. It does everything insert . . . values() does and more.

Collectives™ on Stack Overflow

php ORA-01861: literal does not match format string

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related