STR_TO_DATE format to date
Hi, I was able to extract VARCHAR
to date using string_to_date
, however everything I tried always echoed. 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've tried DATE_FORMAT
similar methods as well but I either got 2009-05-25
it or empty. Can anyone help me to solve this problem. I'm searching and testing, but I decided to ask for help here as help is appreciated and those who have helped in the past have been very kind.
thanks
Sean
a source to share
The return type STR_TO_DATE
is DATE
that which is returned in PHP
.
It's the PHP
one that formats dates in echo
rather than MySQL
.
To do the formatting on the side MySQL
use:
DATE_FORMAT(STR_TO_DATE( pub_date, '%d-%b-%Y'), '%Y.%m.%d')
or in another format.
The internal format controls how your string is expected to be stored in the database, the external format controls how it is output.
To do the formatting on the side PHP
(which is better than you can relate to specific culture formats for different users) use:
echo date('Y m d', strtotime($row['pub_date']))
a source to share
From this referenced site follow the commands and output of mysql
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'
a source to share