MySQL date insert error

Question

I am trying to insert a date that is formatted like 12-Aug-2012 but when I check with database set after insert, I can only see 00-00-0000 on the date field?

Here is the query I use:

$query= "INSERT INTO ambition_opportunities SET

 opp_deadline='".strtotime($_POST['deadline'])."'"; // date field come with date picker** 

 $sql = mysql_query($query) or die(mysql_error());

Thanks

Please read: http://stackoverflow.com/questions/12120433/php-mysql-insert-date-format/12120465#12120465 — Ilia Ross, Aug 26 '12 at 09:10
you need to format your variables so that they match the format of the database field, ie. you can't insert a '12-Aug-2012' formatted date into a 'dd-mm-yyyy' field in the database no more than you can add a char to an int field. Also, try to stay away from `*_mysql` methods ([read the red box here](http://es.php.net/mysql_query)), try to use PDO or MySQLi. Also, your INSERT syntax is wrong, check out the manual [here](http://dev.mysql.com/doc/refman/5.5/en/insert.html) — Stu, Aug 26 '12 at 09:10

score 6 · Answer 1 · edited May 23 '17 at 10:34

As stated in Date and Time Literals:

MySQL recognizes DATE values in these formats:

As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, '2012-12-31', '2012/12/31', '2012^12^31', and '2012@12@31' are equivalent.

As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '20070523' and '070523' are interpreted as '2007-05-23', but '071332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.

As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.

Therefore, the string '12-Aug-2012' is not a valid MySQL date literal. You have three options (in some vague order of preference, without any further information of your requirements):

Configure Datepicker to provide dates in a supported format using its dateFormat option:
```
$( ".selector" ).datepicker({ dateFormat: "yyyy-mm-dd" });
```
Use MySQL's STR_TO_DATE() function to convert the string:
```
opp_deadline=STR_TO_DATE('$_POST[deadline]', '%d-%b-%Y')
```
NB: see the warning below regarding SQL injection.
Convert the string received into a PHP timestamp—e.g. using strtotime() as you currently are:
```
$timestamp = strtotime($_POST['deadline']);
```
and then either:
- format the timestamp using date():
```
$date = date('Y-m-d', $timestamp);
```
- pass the timestamp directly to MySQL using FROM_UNIXTIME():
```
opp_deadline=FROM_UNIXTIME('$timestamp')
```

Warning

Your code is vulnerable to SQL injection. You really should be using prepared statements, into which you pass your variables as parameters that do not get evaluated for SQL. If you don't know what I'm talking about, or how to fix it, read the story of Bobby Tables.
Also, as stated in the introduction to the PHP manual chapter on the mysql_* functions:

This extension is not recommended for writing new code. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API.

You can read and watch more about SQL Injection: http://stackoverflow.com/questions/11939226/sql-injections-and-adodb-library-general-php-website-security-with-examples/11941396 — Ilia Ross, Aug 26 '12 at 09:21

score 0 · Answer 2 · answered Aug 26 '12 at 09:09

As you've mentioned , the field's type in the database is 00-00-0000. You insert a value in the format of: 12-Aug-2012.

You should convert your month's value from "month name" to "number", For instance:

Jan -> 01,
Feb -> 02 ...

You got the point.

After that, it should work.

score 0 · Answer 3 · edited May 23 '17 at 11:55

You must make sure that on your input the date format is YYYY-MM-DD. 12-Aug-2012 is not the valid MySQL date format and this is why it returns an error.

Besides, your code is vulnerable for SQL Injection. I would strongly recommend using mysql_real_escape_string as you insert data into your database to prevent SQL injections, as a quick solution or better use PDO or MySQLi.

Your insert query should rather look like this:

  SET
 opp_provider='".mysql_real_escape_string($_POST['provider'])."'";

score 0 · Answer 4 · answered Dec 24 '13 at 04:29

I am trying to insert date in the textbox 2013-12-24. The date in the database show 1970-01-01

$date=$year & $month & $day; $date= Date("Y-m-d", strtotime($date));

    $query = "INSERT INTO `book` VALUES('$id', '$title', '$author_name', '$publisher', '$date' )";

MySQL date insert error

4 Answers4

Warning