I'm having a lot of trouble formatting the date in MySQL. Can someone help?

Hi,

I'm having problems with my date feature on a news board I'm using. I'm trying to get the date a given article is posted.

Right now the date is featured like this:
20040103190226


In this script, I get that by using this:
<?php echo $row["datePublished"]; ?>


I would like to format the date like this, January 3, 2004.

I tried this:
<?php echo $row["datePublished"]("F j, Y, g:i a"); ?>

But I only get this error message:
Fatal error: Call to undefined function: 20040103190226()

Any suggestions?

Thanks

banx,

There's two ways to approach this... you can either alter your SQL statement to return the desired date, or convert your display data via PHP... either way is generally okay... Here's a couple examples to acheive January 4, 2004

try this:
<?php echo date("F j, Y", $row['datePublished']); ?>

or this:
SELECT DATE_FORMAT(datefield, '%M %e, %Y') FROM table


Please note, I haven't tested either examples, but i'm pretty positive they'll work...

Refer to the respective documention:
http://www.php.net/date
http://www.mysql.com/doc/en/Date_an..._functions.html

I used :
<?php echo date("F j, Y", $row['datePublished']); ?>


But all I got was:
January 18, 2038

Which is the right format, but not the date, and its the same date on all news posts.

What's the column type of the data in Mysql?
have you tried just outputting the data directly?

Or even just a simple:
<?php echo date("F j, Y")." **** ".$row['datePublished']; ?>

This will help debug it... it should display the current date in your format, then whatever data is in the particular date field of the row... seperated by asterisks so you see it easier =)

The column type is "timestamp(14)".

How would I go about outputting the data directly? I'm not too good at all with MySQL/PHP so I wouldn't really know.




What are the "****" supposed to be?


Also, I forgot to add. Its a news script so I'm trying to get the date of when the news report was posted. I can get them on them on there but its all stuck together, like this...20040104823. I'm just trying to figure out to how to make it appear a different way.

I put the stars in just so you could see the difference in the two peices of data... I usually forget and all my variables blend together... they serve no purpose, just for display

Is the data in your table correct? what happens if you simply output $row['datepublished']?

If I put this:

<?php echo date("F j, Y", $row['datePublished']); ?>

I get today's date followed by this: "20040104823"

I tried this:
<?php echo $row["datePublished"]("F j, Y, g:i a"); ?>

But I only get this error message:
Fatal error: Call to undefined function: 20040103190226()


When I use:
<?php echo $row["datePublished"]; ?>

I just get this:
20040103190226

Is that what you meant?

You might need to wrap UNIX_TIMESTAMP() around your datePublished field in your query to get it into an 11-digit unix timestamp, which is what the date() function requires.