Incorrect datetime value:??

[icehawk012]

Alright, for some reason this script I wrote is working with mySQL 5 but not 4. I've been running through it trying to figure out what's going on but I keep getting the same error :

[quote]News could not be added because: Incorrect datetime value: '9.7.2006 1:41am' for column 'date' at row 1[/quote]

So here's what I've got as far as adding the "News":

[code] } else if (isset ($_POST['submit'])) {
include("db2.php");
$realdate = date ( 'n.j.Y g:ia' );
$writenews = "INSERT INTO park_news (title, content, date) VALUES ('{$_POST['posttitle']}', '{$_POST['postcontent']}', '{$realdate}')";
if (@mysql_query($writenews)) {
print ("<p>News successfully added.</p>");
} else {
print ("<p>News could not be added because: <b>" . mysql_error() . "</b></p>");
}
print ("<img src=\"spacer.gif\" width=\"75%\" height=\"1\" />");
[/code]

Should I be using the time() function to get my date and then reformatting it when it spits it back out?

[pocobueno1388]

I just use the NOW() function.

[code]
$realdate = NOW();
[/code]

Although I am not sure how to or even if you can format that to how you want it. It works fine for me for datetime feilds.

[icehawk012]

The now() function won't work. I get the following error when I try to use it:

[quote]Fatal error: Call to undefined function:[/quote]

Any other ideas?

[wildteen88]

now is a mysql function. It is not a PHP function. That is why you get an undefined function.

YOu use NOW() in your MySQL Query

[designationlocutus]

What is the datatype in your database for the date?

[icehawk012]

DATETIME

[icehawk012]

Ok thanks for clearing that up. I used the NOW() function in my MySQL query and it works now, but how would I go about making it my time and not the server time? Or is that even possible?

[designationlocutus]

Yeah its possible. PHP5 introduced some date/time contants to use. You will probably have to use php date function in order to get them to work properly and then add to the database afterward.

http://uk.php.net/manual/en/ref.datetime.php#datetime.constants

[icehawk012]

Ok, I've got it all figured out so I figured I would update everyone on how it's done. I'm using the MySQL NOW() function, but combining it with the MySQL CONVERT_TZ() function which takes three variables: DATETIME, Timezone1, and Timezone2. Timezone1 being the starting timezone and Timezone2 being the timezone to be converted to. The code ends up looking like so:

[code] } else if (isset ($_POST['submit'])) {
include("db2.php");
$writenews = "INSERT INTO park_news (title, content, date) VALUES ('{$_POST['posttitle']}', '{$_POST['postcontent']}', CONVERT_TZ(NOW(),'+00:00','-07:00'))";
if (@mysql_query($writenews)) {
print ("<p>News successfully added.</p>");
} else {
print ("<p>News could not be added because: <b>" . mysql_error() . "</b></p>");
}
print ("<img src=\"spacer.gif\" width=\"75%\" height=\"1\" />");
[/code]

Works flawlessly.  ;D