Selecting a single date per day only from date and time (YYYY-MM-DD HH: MM: SS) in MySQL

Question

Running this command brings me the following (all dates from all columns, so it essentially did the same thing as SELECT date without a separate one):

SELECT DISTINCT date FROM daily ORDER BY date DESC

2013-02-12 16:40:52
2013-02-06 11:48:49
2013-02-06 11:36:41
2013-02-06 11:35:59
2013-02-04 19:38:12
2013-02-04 18:12:30
2013-02-04 09:58:41
2013-02-04 09:43:01
2013-02-04 09:35:51
2013-02-04 09:30:22
2013-02-04 09:24:57
2013-02-04 09:21:09
2013-02-04 08:50:13

What I need:

2013-02-12
2013-02-06
2013-02-04

• Is there a way to change my date table and convert it to YYYY-MM-DD?

• If not, is there a way to select individual dates based only on the bottom?

Answer 1

mysql> select DATE_FORMAT(Current_Timestamp, '%c %d %Y') from dual;
+--------------------------------------------+
| DATE_FORMAT(Current_Timestamp, '%c %d %Y') |
+--------------------------------------------+
| 2 12 2013                                  |
+--------------------------------------------+
1 row in set (0.01 sec)

mysql> 

Of course you would use your daily table.

mysql> select DATE_FORMAT(Date, '%c %d %Y') from daily;

or maybe you want

mysql> select * from daily group by DATE_FORMAT(Date, '%c %d %Y');

Answer 2

Try the following:

SELECT DISTINCT(CONVERT(VARCHAR, CONVERT(DATETIME,[DATE]),23)) AS DT  
FROM DAILY
ORDER BY DT ASC

Answer 3

> N , DATE_FORMAT 10-20% , ;

 GROUP BY someid,year(date),month(date),day(date)

Answer 4

For MySQL, you can use DATE(date)only dates to return. If you want to format it, use DATE_FORMAT:

SELECT DISTINCT DATE_FORMAT(yourdate, '%Y-%m-%d') 
FROM daily ORDER BY DATE(yourdate) DESC

You cannot change the way these values ​​are stored in the database.

And here is the fiddle: http://sqlfiddle.com/#!2/f50527/6

Answer 5

In Access DB

SELECT DISTINCT Format(date, 'Short Date')
  FROM daily
 ORDER BY Format(date, 'Short Date') DESC;