按字符串日期搜索mysql

Question

我需要运行按日期范围返回行的查询。日期是以字符串格式保存的，比如"22/11/2017“，我试着在str_to_date中使用，但效果不是很好。我的问题是：

SELECT Activities.Date AS 'dates'  FROM Attendance
                           INNER JOIN Activities ON Activities.ID=Attendance.ActivitiesID
                           INNER JOIN Groups ON Activities.GroupID=Groups.ID
                           INNER JOIN Projects ON Projects.ID=Groups.ProjectID
                           WHERE Attendance.Attendance <>'' AND Projects.ID=16 AND  (str_to_date(Activities.Date,'%d/%m/%Y')>="01/11/2017" AND str_to_date(Activities.Date,'%d/%m/%Y')<='30/11/2017') GROUP BY Activities.Date
                            ORDER BY Groups.Name

结果是：

'24/09/2017','24/10/2017','24/11/2017','24/12/2017','25/09/2017','25/10/2017,..