CODEWITHSUNDEEP
phpmysqlstringdate
Ddywalgi
Ddywalgi

Reputation: 45

Format STR_TO_DATE to a date

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

Upvotes: 1

Views: 10132

Answers (2)

zeeawan
zeeawan

Reputation: 6895

From this reference site following are mysql command and output

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
    ->                 '%D %y %a %d %m %b %j');
        -> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
    ->                 '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425261

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']))

Upvotes: 2

Related Questions