CODEWITHSUNDEEP
phpmysqldate
blsn
blsn

Reputation: 1093

date format in MySQL table

I have the following two names in MySQL table:

message is type text

date is type datetime

<?php
while($row=mysql_fetch_array($result)){
?>
<tr>
     <td><div><a><? echo $row['message'] ?></a></div></td>
     <td><div><a><? echo $row['date']; ?></a></div></td>
</tr>
<?php
 }
?>

The output of the above table shows date in this format 2012-08-05 17:43:57

How to print date in this format 2012-08-05 (without time)?

I cannot change type datetime in MySQl (it’s required in different pages in different formats).

I’ve tried the following, but it doesn’t help (no printing at all)

<td><div><a><? echo $rows['date'](“Y-m-d”); ?></a></div></td>

p.s. other combinations of date format give me syntax error.

Any suggestion would be much appreciated,

Upvotes: 1

Views: 2804

Answers (7)

simme
simme

Reputation: 1554

You first need to convert it to a date:

$tmpdate = strtotime($row['date']);
$date = date('Y-M-d', $tmpdate);
echo $date;

Of course you may short it down, as others suggested, but i prefer "the long way" to improve readability and help you get up to speed faster when you go back and review old code in the future. :-)

Upvotes: 0

user557846
user557846

Reputation:

every one else is suggesting you do it with php, i always do this with mySQL's own date function:

SELECT ... DATE_FORMAT(Date_Field ,'%Y-%e-%c') ...

DATE_FORMAT

Upvotes: 0

Ruzbeh Irani
Ruzbeh Irani

Reputation: 2438

http://php.net/manual/en/datetime.format.php

 <td><div><a><? echo new DateTime($row['date'])->format('Y-m-d'); ; ?></a></div></td>

Upvotes: 0

Diego
Diego

Reputation: 16714

You should use format function

echo new DateTime($row['date'])->format('Y-m-d');

UPDATED thanks to @Erty

Upvotes: 0

Sahil
Sahil

Reputation: 9480

There is something called TO_CHAR method in MS-SQL and Oracle in which you can pass the format you want your date in, so while fetching the data you can change it

Upvotes: 0

Biotox
Biotox

Reputation: 1601

You want the date() to change the format.

Upvotes: 0

Erty Seidohl
Erty Seidohl

Reputation: 4549

You want 

<?php echo date('Y-m-d', strtotime($row['date'])); ?>

strtotime converts your datestring into a unix-timestamp

then, the date function formats it properly according to the string.

Upvotes: 3

Related Questions