How to fix "Incorrect date value: '2020' for column 'StartDate' at row 1" when trying to insert date into database

Question

So I'm trying to insert data from a form which are, an id, 2 dates, a string, and a decimal. but it gives the error above.

columns are: ID:int, startdate:Date, enddate:Date, number:int, string, Decimal.

I have already tried adding all those date() functions and strtodate() and stuff like that, but nothing worked.

if (isset($_POST['submitReservation'])) {
    $startDate = $_POST['startDate'];
    $endDate = $_POST['endDate'];

    $linens = mysqli_real_escape_string($conn, $_POST['linens']);
    $peopleAmount = mysqli_real_escape_string($conn, $_POST['persons']);
    $number = 50;
    $userID = $_SESSION["UserID"];

    $sql = $conn->prepare("INSERT INTO reservation (TenantId, StartDate, EndDate, Number_Of_Persons, Linens, Cost) VALUES (?, ?, ?, ?, ?, ?)");
    $sql->bind_param("iiiisd", $userID, $startDate, $endDate, $peopleAmount, $linens, $number);
    $sql->execute() or die(mysqli_error($conn));
}

It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Jun 14 '20 at 19:38
What have you tried to debug the problem? Which value do you try to insert into that `StartDate` column? — Nico Haase, Jun 15 '20 at 05:18

musashii · Accepted Answer · 2020-06-14T19:56:15.910

1

You are casting the dates to int $sql->bind_param("iiiisd"

Edit: MySQL expects a string, so try $sql->bind_param("issisd"

edited Jun 14 '20 at 19:56

answered Jun 14 '20 at 19:47

musashii

445
6
13

hmm, so what should I use, cuz I can only use: string, int, double, blob – YesMyDude Jun 14 '20 at 19:49
use string, that´s what MySQL is expecting – musashii Jun 14 '20 at 19:56
1

I dont know why your answer got downvoted because it worked – YesMyDude Jun 14 '20 at 20:00

score -2 · Answer 2 · answered Jun 14 '20 at 19:56

If you are using the standard PDO library you can use bindParam() or bindValue() and specify the type e.g.

if (isset($_POST['submitReservation'])) {
    $startDate = $_POST['startDate'];
    $endDate = $_POST['endDate'];

    $linens = $_POST['linens']);
    $peopleAmount = $_POST['persons']);
    $number = 50;
    $userID = $_SESSION["UserID"];

    $sql = $conn->prepare("INSERT INTO reservation (TenantId, StartDate, EndDate, Number_Of_Persons, Linens, Cost) VALUES (?, ?, ?, ?, ?, ?)");
    $sql->bindParam(1, $userID, PDO::PARAM_STR);
    $sql->bindParam(2, $startDate, PDO::PARAM_STR);
    $sql->bindParam(3, $endDate, PDO::PARAM_STR);
    $sql->bindParam(4, $peopleAmount, PDO::PARAM_INT);
    $sql->bindParam(5, $linens, PDO::PARAM_STR);
    $sql->bindParam(6, $number, PDO::PARAM_INT);
    $sql->execute();
}

I see you are using mysqli, you should probably change this to use PDO instead but if you have to use it for some reason then musashii is correct it needs to be cast as string — Ash Marchington, Jun 14 '20 at 20:01

How to fix "Incorrect date value: '2020' for column 'StartDate' at row 1" when trying to insert date into database

2 Answers2