CODEWITHSUNDEEP
mysqlsqldate
Omar Shizzle
Omar Shizzle

Reputation: 1

Removing HH:MM:SS from MYSQL Result

SQL query returns the following:

    User Registered On     Last visit
    2011-10-08 23:51:08    2011-10-08 23:51:09
    2007-07-28 16:26:57    2013-10-05 11:51:42

I want to truncate the result to only YYYY-MM-DD so I can compare the login dates to see if the user is somewhat active. I've looked at CAST and SUBSTRING, but I just can't get things to work!

How would I remove the HH:MM:SS from the result? Tearing my hair out over this!

$query = "select * 
FROM    users
";

$result = mysql_query($query);
echo "<table>";
echo "<tr><td>" . "User Registered On" . "</td><td>" . "Last visit" . "</td></tr>";
while($row = mysql_fetch_array($result)){

echo "<tr><td>" . $row['registerDate'] . "</td><td>" . $row['lastvisitDate'] . "</td></tr>";

}

echo "</table>";
mysql_close();

Upvotes: 0

Views: 1090

Answers (2)

Tamil Selvan C
Tamil Selvan C

Reputation: 20219

Try

In php way:

date('Y-m-d', strtotime($row['registerDate']))

In mysql way : use DATE_FORMAT(), DATE():

SELECT DATE_FORMAT(registerDate, '%Y-%m-%d'), DATE_FORMAT(lastvisitDate, '%Y-%m-%d') FROM users;

Upvotes: 3

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

In MySQL use DATE() function on your datetime column

Upvotes: 1

Related Questions