select distinct timestamp as DD/MM/YYYY mysql

Question

I have multiple rows for itemDate (timestamp) like below:

itemDate
2013-04-09 17:24:31
2013-04-09 10:24:31
2013-04-08 12:20:30

When I use SELECT DISTINCT(itemDate) FROM item;, I am getting two rows for 2013-04-08.

I want to get the date of different date (based on YYYY-MM-DD) ignoring whether the time is different for the same day.

The result that I need should be like the following, when I query from that table:

itemDate
2013-04-09
2013-04-08

Answer 1

That's because those values are different. If you're trying to do this by date then use the DATE() function:

SELECT DISTINCT(DATE(itemDate)) FROM item;

Answer 2

An alternative from the other answer is to use DATE_FORMAT()

SELECT DATE_FORMAT(itemDate, '%Y-%m-%d') ItemDate 
FROM   Item
GROUP  BY DATE_FORMAT(itemDate, '%Y-%m-%d')

http://www.sqlfiddle.com/#!2/bfaf5e/3

Answer 3

On my side, I have tried the different options suggested above but the Sybase DB I use is rejecting me:

Error: Function 'DATE' not found. If this is a SQLJ function or SQL function, use sp_help to check whether the object exists (sp_help may produce a large amount of output).

My colleague gave me this and works OK

SELECT distinct(CONVERT(varchar(20), timestamp, 111)) FROM HistoOrder

I use 111 but you can find code list here http://www.w3schools.com/sql/func_convert.asp