Format STR_TO_DATE to a date.

Question

Hi I have been able to extract a VARCHAR to a date using string_to_date, however whatever I try the date always echoes as e.g. 2009-05-25
Here is my code that works:

$query = "SELECT u.url_id, url, title, description, STR_TO_DATE( pub_date, '%d-%b-%Y') 
    AS pub_date FROM urls AS u, url_associations AS ua WHERE u.url_id = ua.url_id AND
    ua.url_category_id=$type AND ua.approved = 'Y' ORDER BY pub_date DESC";
$result = mysql_query ($query);
echo "  <tr>
<td align=\"left\">{$row['pub_date']}</td>
</tr>\n";

I have tried DATE_FORMAT and similar methods but I either receive 2009-05-25 or blank. Can anyone help me solve this issue. I am looking and testing but I have decided to ask for help here as the assistance is welcomed and those who helped previously were very kind.

Thanks

Sean

Answer 1

The return type of STR_TO_DATE is DATE which is returned to PHP.

It's PHP that formats dates in your echo, not MySQL.

To do the formatting on MySQL side, use:

DATE_FORMAT(STR_TO_DATE( pub_date, '%d-%b-%Y'), '%Y.%m.%d')

or other format.

The inner format controls how your string is expected to be stored in the database, the outer format controls how will it be output.

To do the formatting on PHP side (which is better as you can honor culture specific formats for different users), use:

echo date('Y m d', strtotime($row['pub_date']))

Answer 2

this site will help format dates using the mysql date_format function http://www.mysqlformatdate.com