[RESOLVED] Date Format for MySql

[KTech]

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

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.

[penagate]

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).

[KTech]

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.

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

[KTech]

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

[KTech]

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.


$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')

[kows]

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.
$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')";