Date Format Trouble

[refiking]

I am trying to change the format of my db stored date.  I echoed both the $date variable and the $time variable to show you that the format I'm looking for is there, but it isn't giving the right date and time.  Here is the code:

 

while($ran = mysql_fetch_array($li)){
$lid = $ran['lid'];
$date = $ran['dtime'];
$time = date("m/d/y g:i A", $date);
}
Echo $date."<br>".$time;

 

Here's what it returns:

2008-04-29 21:30:00

12/31/69 7:33 PM

[hitman6003]

Use MySQL's DATE_FORMAT function.

 

The php date function expects a unix timestamp (seconds since epoc).  However, MySQL is outputting a string representation of the date.  You can either change the format in MySQL (using the DATE_FORMAT function), or you can use MySQL's UNIX_TIMESTAMP function to convert it to that format and then format the final in php.

 

I recommend the former:

 

SELECT DATE_FORMAT(dtime, "%m/%d/%y %g:%i %A") FROM table_name

 

I'm not 100% sure that the format string is correct however (I didn't look up the format).

 

http://www.mysql.com/date_format

[refiking]

Here is what I tried based on the link you provided:

$dql = mysql_query('SELECT DATE_FORMAT(dtime, "%m/%d/%y") FROM dlg WHERE lname = "$lname"');
$dql2 = mysql_num_rows($dql);
while ($run = mysql_fetch_assoc($dql)){
$dtime = $run['dtime'];
//$time = date("m/d/y g:i A", $dtime);
}
Echo $dql2."<br>".$dtime;

 

It returned 0 (zero).  So it's not able to retrieve the data that way.  Is there something I am missing or should take away?

[hitman6003]

what is the data type of the field?

[refiking]

datetime

[hitman6003]

First, by "it returned zero" do you mean it returned zero rows, or it failed?

 

Second, your query is probably wrong...you have it enclosed in single quotes, which means it will not do variable substitution...

 

change

 

$dql = mysql_query('SELECT DATE_FORMAT(dtime, "%m/%d/%y") FROM dlg WHERE lname = "$lname"');

 

to

 

$dql = mysql_query("SELECT DATE_FORMAT(dtime, '%m/%d/%y') FROM dlg WHERE lname = '$lname'");

 

Notice I changed double quotes to single, and single to double.

[refiking]

OK. Here's the code I put in:

 

$dql = mysql_query("SELECT DATE_FORMAT(dtime, '%m/%d/%y') FROM dlg WHERE lname = '$lname'");
$dql2 = mysql_num_rows($dql);
while ($run = mysql_fetch_assoc($dql)){
$dtime = $run['dtime'];

}
Echo $dql."<br>".$dql2."<br>".$dtime;

 

Here's what it returned

Resource id #4

1

[craygo]

Hitman have you the mysql way to do it. if you want to do it with php, the date function requires the unix timestamp to give you the correct date. Since the date in mysql is a date and not a timestamp you have to convert it.

$time = date("m/d/y g:i A", strtotime($dtime));

 

Ray

 

[craygo]

To use what you have above.

 

$dql = mysql_query("SELECT DATE_FORMAT(dtime, '%m/%d/%y') AS dtime FROM dlg WHERE lname = '$lname'"); // add "AS dtime"
$dql2 = mysql_num_rows($dql);
while ($run = mysql_fetch_assoc($dql)){
$dtime = $run['dtime'];

}
Echo $dql."<br>".$dql2."<br>".$dtime;

 

Ray

[hitman6003]

$dg1 is a resource, so it being equal to "Resource id #4" is normal.

 

$dg12 is the number of rows returned..."1" means one row was returned.

 

You didn't get a value returned back for $dtime because mysql is returning the column name back with the name "DATE_FORMAT....".  Either use a numeric index on your result...

 

while ($row = mysql_fetch_array($dg1)) {
  $dtime = $row[0];
}

 

Or, since you have one row, with one column, use mysql_result:

 

$dtime = mysql_result($result, 0);

 

Or assign the result column to a different name in the SQL query:

 

SELECT DATE_FORMAT(dtime, '%m/%d/%y') AS dtime FROM dlg WHERE lname = '$lname'

[refiking]

Thanks Craygo and Hitman.  I used the php version and it works perfectly