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

Question

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?

@user693121 - Is this MS Sql Server?

Joshua Carmody
– Joshua Carmody

2011-04-06 14:35:00 +00:00
Commented Apr 6, 2011 at 14:35 — Joshua Carmody
– Joshua Carmody, Commented Apr 6, 2011 at 14:35
Yes. And it turns out this is a DATETIME field NOT a string

Demasterpl
– Demasterpl

2011-04-06 14:54:24 +00:00
Commented Apr 6, 2011 at 14:54 — Demasterpl
– Demasterpl, Commented Apr 6, 2011 at 14:54

trickwallett · Accepted Answer · 2011-04-06 14:30:24Z

4

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

answered Apr 6, 2011 at 14:30

trickwallett

2,45817 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Demasterpl Over a year ago

DATE_FORMAT' is not a recognized built-in function name :(

ypercubeᵀᴹ Over a year ago

DATE_FORMAT is indeed a MySQL function. You must have added an extra quote or something.

Demasterpl Over a year ago

DATE_FORMAT is not compatible with Microsoft SQL 2005. Trying to use a variation of "CONVERT(VARCHAR(10), column, 101) AS [MM/DD/YYYY]" but VARCHAR and CHAR are not recognized either.

Isotopp · Accepted Answer · 2011-04-06 14:34:31Z

1

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

answered Apr 6, 2011 at 14:34

Isotopp

3,4131 gold badge18 silver badges17 bronze badges

1 Comment

Demasterpl Over a year ago

Turns out they are DATETIME fields. What would I apply?

Sourav · Accepted Answer · 2011-04-06 14:31:59Z

0

i think you can use substring !!!

SELECT SUBSTRING(TimeStamp,7,10);

answered Apr 6, 2011 at 14:31

Sourav

17.6k36 gold badges109 silver badges163 bronze badges

Comments

bensiu · Accepted Answer · 2011-04-06 14:32:09Z

0

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

answered Apr 6, 2011 at 14:32

bensiu

25.7k56 gold badges81 silver badges117 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related