CODEWITHSUNDEEP
mysqlsqlstring
Demasterpl
Demasterpl

Reputation: 2133

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?

Upvotes: 1

Views: 13516

Answers (4)

Isotopp
Isotopp

Reputation: 3403

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

Upvotes: 1

bensiu
bensiu

Reputation: 25604

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

Upvotes: 0

Sourav
Sourav

Reputation: 17530

i think you can use substring !!!

SELECT SUBSTRING(TimeStamp,7,10);

Upvotes: 0

trickwallett
trickwallett

Reputation: 2468

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")

Upvotes: 4

Related Questions