Invalid datetime format: 1292 Incorrect datetime value

Question

I am getting below error when I am trying to update a table with a field(datetime)

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[2007]: Invalid datetime format: 1292 Incorrect datetime value: '02-27-2017 16:37' for column lastupated

My PHP code uses PDO

$lastupdated = date('m-d-Y H:i:s');
$run = $conn->prepare($sql);
$run->bindParam(':lastupdated', $lastupdated, PDO::PARAM_STR);

the SQL the lastupdated, datatype is datetime

Existing data

The dateformat is clearly wrong. Correct would be year-month-day — baao, Feb 27 '17 at 15:55

JazZ · Accepted Answer · 2017-02-27T16:02:41.930

54

You need to format date like "Y-m-d H:i:s" in order to work with MySQL datetime field.

i.e. :

$lastupdated = date('Y-m-d H:i:s');

From documentation :

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

edited Feb 27 '17 at 16:02

answered Feb 27 '17 at 15:57

JazZ

4,469
2
20
40

3

From the [documentation](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-literals.html): Any punctuation character may be used as the delimiter between date parts. But the real issue is his ordering of the date parts. – Tim Biegeleisen Feb 27 '17 at 16:00
Thank you @TimBiegeleisen. Didn't know that. – JazZ Feb 27 '17 at 16:03
1

Neither did I. And I'm glad nobody actually uses `2017@02@28` as a date literal :-) – Tim Biegeleisen Feb 27 '17 at 16:04
@TimBiegeleisen and Jazz thanks, both of you, for the help, it worked. – user580950 Feb 27 '17 at 16:05
@TimBiegeleisen Of course. I used to write date like 2017/02/28. This will be usefull ! – JazZ Feb 27 '17 at 16:06
You're welcome @user580950. Thanks for validate my answer if it solved your question. Good luck. = ) – JazZ Feb 27 '17 at 16:07

Invalid datetime format: 1292 Incorrect datetime value

1 Answers1

Linked