Problems with Date Format

[alan4573]

Hi all,

 

I'm trying to get a date from a database and display it in the following format

 

1st January 2010.

 

The field in the database is datetime format and is called "date_entered"

 

The following code:

 

//build query
$query  = "SELECT date_entered  FROM table WHERE field1='xxx' AND field2='yyy'";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))

$formatteddate = ($row['date_entered']);

echo $formatteddate

 

Returns the following:

 

2010-04-14 09:42:55

 

Which is the correct record showing the correct date.

 

Now as I said, I wanted it formatting to 1st January 2010 format, so I tried this:

 

//build query
$query  = "SELECT date_entered  FROM table WHERE field1='xxx' AND field2='yyy'";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))

$formatteddate = ($row['date_entered']);

echo date("jS F Y", $formatteddate);

 

Which returns:

 

1st January 1970

 

The formatting is correct but the date is wrong.

 

Anyone got any ideas what I'm doing wrong?

 

Thanks in advance

 

Alan

[cags]

The second parameter of the date function needs to be a timestamp, which is not what you are passing it. Passing the date string you have through strtotime would probably fix that.

 

echo date("jS F Y", strtotime($formatteddate));

Having said that, ideally you should format it with MySQL instead.

 

$query  = "SELECT DATE_FORMAT(date_entered, '%D %M %Y') AS nice_date  FROM table WHERE field1='xxx' AND field2='yyy'";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)) {
   echo $row['nice_date'];
}

[JAY6390]

Use MySQL's built in date formatter

//build query
$query  = "SELECT DATE_FORMAT(`date_entered`, '%D %M %Y) as `formatted_date`  FROM table WHERE field1='xxx' AND field2='yyy'";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo $row['formatted_date'];
}

 

EDIT: Cags beat me to it

[alan4573]

Thanks Guys,

 

I had already tried formatting it with MySQL but I missed the AS clause off the end

 

I was using;

$query  = "SELECT DATE_FORMAT(date_entered, '%D %M %Y') FROM.........

 

Instead of

 

$query  = "SELECT DATE_FORMAT(date_entered, '%D %M %Y') AS 'date_entered' FROM.....

 

Doh....

 

Cheers again

[alan4573]

Anybody know how I mark this as solved?

 

Cheers

[cags]

The button labelled 'Mark Solved' located at the bottom left of threads you started.