datetime issue....

crawfd

I have a field setup in a table that is set to datetime (0000-00-00 00:00:00). Everything works fine and dandy except for the fact that it's not differentiating am and pm. It always posts the time in a.m.?

I have the following query being performed to format the date:

<?
$q4 = "select *, DATE_FORMAT(dateadd, '%b-%D-%Y, %h:%i:%s %p')dateadd from usrcomments, class_members where usrcomments.commentid = '$_GET[id]' and usrcomments.commentid = class_members.MemberID and sect = '1' ORDER BY id DESC LIMIT 15";
$r4 = mysql_query($q4) or die(mysql_error());
?>

sneakyimp

the question you have posted is more suitable to the database forum i think.

do usrcomments and classmembers tables have any fields in common? you might want to request fields specifically by field name otherwise one table might over write the fields in the other if there are any field name collisions.

you might also want to given a different alias to the DATE_FORMAT result...something other than dateadd...maybe formatted_date or something.

other wise:

<? 
$q4 = "select * from usrcomments, class_members where usrcomments.commentid = '$_GET[id]' and usrcomments.commentid = class_members.MemberID and sect = '1' ORDER BY id DESC LIMIT 15"; 
$r4 = mysql_query($q4) or die(mysql_error()); 

$row = mysql_fetch_assoc($r4);
$parts = explode(' ', $row['dateadd']);
$date_parts = explode('-', $parts[0]);
$time_parts = explode(':', $parts[1]);
$stamp = mktime($time_parts[0],
                             $time_parts[1],
                             $time_parts[2],
                             $date_parts[1],
                             $date_parts[2],
                             $date_parts[0],
);
echo date('M-jS-Y h:i:s A');
?>

crawfd

I'd like to use the dateformat option. Does it have anything to do with how I'm saving the date in my query? I'm using this currently:

<?
$date = Date("y-m-d h:i:s A");

$q1 = "insert into usrcomments set commentid = '$_GET[id]', sect = '$_POST[sect]', comments = '$_POST[comments]', dateadd = '$date'";

mysql_query($q1);

?>

sneakyimp

YES. definitely. You have to use 24-hr time when saving a DATETIME field. You're also using a 2-digit year which probably isn't good.

use this:

$date = Date("Y-m-d H:i:s");

That's uppercase Y, uppercase H