[RESOLVED] Date Format for MySql

I think the most common problem with any new language is understanding how the dates are formatted. I'm making my first PHP website and trying to insert a date into MySQL but just can't seem to find an example of how to do it anywhere.
Here is what I have so far

Code:

---

if (isset($_REQUEST['txtDate'])) {
        $dt = split ("/",$_REQUEST['txtDate']);
        $MonthValue = $dt[0];
        $DayValue = $dt[1];
        $YearValue = $dt[2];
        if (checkdate($MonthValue,$DayValue, $YearValue)) {
          $insertrecord = "Insert into tblTime (CategoryId, DateCreated) values {
          $insertrecord .= $_REQUEST['selCategory'];
          $insertrecord .= ", ";
          $insertrecord .= date('Y-m-d', strtotime($_REQUEST['txtDate']));
            $insertrecord .= ")";

        }
  }
}

---

When I execute the statement it succeeds but I get a record like

Category = 1
DateCreated = 0000-00-00

The first part of the code validates the date entered to ensure it is valid so I know that I have a valid date + I entered it so I know it is valid. It passes the validation test but I have tried about a dozen different ways of formatting the date to enter it and it just does not make it into the database.

I'm sure there is someone out there who has entered a date other than the current time into a MySQL database but unfortunately there are no references to this problem anywhere on the internet.

What type is the DateCreated column?

Y-m-d is the right format for a datetime column but it might simply be that you are missing the quotes around the value.
insert into foo values ('2009-04-17');
Try echoing the SQL string and executing it in a MySQL console.

Asides: I avoid Hungarian notation. (It doesn't help much in a strongly-typed language, even less in duck-typed ones like PHP, and not at all in HTTP requests. In fact, in the latter case it can be downright dangerous: you might forget to validate that a field is numeric because it has a prefix like 'int' or 'num'.) I also avoid building SQL queries with strings. If you can, use a data access library that supports parameterised queries. (PDO if available, else mysqli for MySQL only).

Here is what the insert command looks like and it does successfully insert into the database but with the date being blank. The DateCreated column is a datetime datatype.

Code:

---

Insert into tblTime (CategoryId, UserId, TimeSpent, Wages, DateCreated)
values (2, 7, 20, 20, date_format(3/5/2009,'%d%m%Y'))

---

If I put quotes around the date value I get the error cannot insert null into DateCreated column

Hang on that is a different format than I originally posted. OK, when I executed this statement it actually entered a date but it doesn't match the date that was entered.

Code:

---

$insertrecord = "Insert into tblTime (CategoryId, UserId, TimeSpent, Wages, DateCreated) values (";
          $insertrecord .= $_REQUEST['selCategory'];
          $insertrecord .= ", ";
          $insertrecord .= $_SESSION['PoopTimeId'];
          $insertrecord .= ", ";
          $insertrecord .= $_REQUEST['txtMinutes'];
          $insertrecord .= ", ";
          $insertrecord .= $_REQUEST['txtWages'];
          $insertrecord .= ", '";
          $insertrecord .= date('Y-m-d', $_REQUEST['txtDate']);
          $insertrecord .= "')";

Produces this statement:
Insert into tblTime (CategoryId, UserId, TimeSpent, Wages, DateCreated)
values (1, 7, 20, 40, '1969-12-31')

---

strtotime() doesn't really work the way you'd like it to, and the date() function takes a unix timestamp as the second argument (whereas you are providing something else). since you're already splitting up your date, though, you can just use your $dt array to format the date the way you want it.

PHP Code:

---

$mydate = "{$YearValue}-{$MonthValue}-{$DayValue}"; //produces Y-m-d format

 $insertrecord = "Insert into tblTime (CategoryId, UserId, TimeSpent, Wages, DateCreated) values (";
          $insertrecord .= $_REQUEST['selCategory'];
          $insertrecord .= ", ";
          $insertrecord .= $_SESSION['PoopTimeId'];
          $insertrecord .= ", ";
          $insertrecord .= $_REQUEST['txtMinutes'];
          $insertrecord .= ", ";
          $insertrecord .= $_REQUEST['txtWages'];
          $insertrecord .= ", '$mydate')"; 


---