PHP Error Uncaught mysqli_sql_exception: Incorrect date value: when sending Date to MySQL

Question

I have spent the afternoon trying various solutions but despite numerous questions on 'SO', I have not been able to resolve this issue.

My PHP code is below where I want to INSERT dates into a mySQL table, the table only has 3 columns as per screenshot.

The dates arrive in php already formatted as 2020-10-03. My understanding was this would be sufficiently correct to send them directly to mySQL, however I have tried to convert them in different ways, but I still receive the following error:

include(dirname(__DIR__).'/php/dbConnection.php');

ini_set('display_errors', 'On');
error_reporting(E_ALL);

//values passed from ajax
// $id         = $_POST['staffID'];
// $fromDate   = $_POST['startDate'];
// $endDate    = $_POST['endDate'];

//for testing > This Fails
$id         = '22';
$fromDate   = DateTime::createFromFormat('d-m-Y', '10-06-2020')->format('Y-m-d');
$endDate    = DateTime::createFromFormat('d-m-Y', '12-06-2020')->format('Y-m-d');

//for testing > This Fails
//$id         = '22';
//$fromDate   = '2020-06-30';
//$endDate    = '2020-07-30';

$data       = array();      // array to pass back data

$stmt = $conn->prepare("INSERT INTO vacation (id, from_date, to_date)
                        VALUES ($id, $fromDate, $endDate)");
$stmt->execute();

$data['success'] = true;
$data['message'] = 'Success!';


echo json_encode($data);

mysqli_close($conn);

appreciate any guidance on where I am going wrong and how I need to interpret this error message.

Cheers

*Unquoted* `2020-06-30` is an arithmetic operation, not a date - that yields `1984`. Bottom line: use prepared statements, and you won't have to worry about such problem, while making your code safer and more efficient. — GMB, Dec 19 '20 at 17:34
See [here](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) for `mysqli` or [here](https://www.php.net/manual/en/book.pdo) for `PDO` (recommended) prepared statements. — El_Vanja, Dec 19 '20 at 17:52
@GMB thanks for the clarification I did wonder where 1987 was been generated. — trig79, Dec 19 '20 at 18:11

S.Sachith · Accepted Answer · 2020-12-19T18:12:12.033

You can use prepared statement so you don't need to convert the date and also it's more secure because it's prevent SQL injection so here is the changed code


include(dirname(__DIR__).'/php/dbConnection.php');

ini_set('display_errors', 'On');
error_reporting(E_ALL);

//values passed from ajax
// $id         = $_POST['staffID'];
// $fromDate   = $_POST['startDate'];
// $endDate    = $_POST['endDate'];

//for testing > This Fails
$id         = '22';
$fromDate   = '10-06-2020'
$endDate    = '12-06-2020'

//for testing > This Fails
//$id         = '22';
//$fromDate   = '2020-06-30';
//$endDate    = '2020-07-30';

$data       = array();      // array to pass back data

$stmt = $conn->prepare("INSERT INTO vacation (id, from_date, to_date)
                        VALUES (?, ?, ?)");
$stmt->bind_param("sss", $id, $fromDate, $endDate);

$stmt->execute();

$data['success'] = true;
$data['message'] = 'Success!';


echo json_encode($data);

$stmt->close();
$conn->close();

PHP Error Uncaught mysqli_sql_exception: Incorrect date value: when sending Date to MySQL

1 Answers1