How to GROUP BY date,date's format is nvarchar

for example:

mydate is nvarchar(20)

mydate
-------------
2011/08/04 10:14:47
2011/07/20 15:00:00
2011/07/20 22:15:59
2011/08/04开发者_JAVA百科 13:17:33

how to group up by date

for example:

mydate
------------
2011/08/04
2011/07/20

---

Assuming tou're using SQL Server, try this:

SELECT LEFT(date_col,10) AS mydate, COUNT(*) 
FROM your_table
GROUP BY LEFT(date_col,10);

---

Exactly the same way as usual should do it. If I'm not mistaken the varchar will sort is alphabetically which is in this case the same. I would also be a good idea to change your database structure to a date instead of a char.

---

In Oracle you can use the following example:

select substr(mydate, 1, 10), count(*)
  from mytable
 group by substr(mydate, 1, 10)

---

SQL Server - Try this,

SELECT convert(varchar, getdate(), 111) AS mydate
FROM yourtable
GROUP BY convert(varchar, getdate(), 111)