MySQL: SQL Query to return Date from Date/Timestamp Column

In a table I have a stored string column "MM/DD/YYYY HH:MM:SS" and I'm looking for a query to return just the "MM/DD/YYYY" part.

Any 开发者_开发知识库ideas?

If the column type is char or varchar, then

SELECT LEFT(colname, 10)

will suffice. If it's a datetime type, then try

SELECT DATE_FORMAT(colname , "%d/%m/%Y")

You should be using a MySQL DATETIME field instead of a string field, really. That would allow you to apply date and time functions, which help tremendously when dealing with temporal data.

In your case, since your data is a string type, and not in MySQL Isodate format (YYYY-MM-DD), you can work only using string functions like SUBSTRING() and specialisations thereof (LEFT, ...).

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html Date and Time functions overview

http://dev.mysql.com/doc/refman/5.5/en/string-functions.html String functions

i think you can use substring !!!

SELECT SUBSTRING(TimeStamp,7,10);

if it is a string use SUBSRT() or LEFT() to extract required part, however it would be wise to have it stored as datatime type