-1

I am working on a project where I have two tables-one is tbllessontopic which saves unique lesson plans and another table tbllessonperiodallocation which saves dates for which the lesson plan will be taught.Now I have applied the query to get all the dates of particular lesson plan in one column named as date by using the group_concat() function.Now I am trying to arrange the dates in this column in ascending order,my column date in tbllessonperiodallocation is of var char type so while trying to convert it in datetime format I am getting the error incorrect date time value.Please suggest how I can achieve this.

Query

select l.id,l.topic,l.dateallocated,l.noofperiods,s.subjectname,c.classname,
group_concat(p.date order by DATE_FORMAT(STR_TO_DATE(p.date, '%d %m %Y'), '%Y-%m-%d') asc)  as date from tbllessontopic l
join tbllessonperiodallocation p on p.lessontopicid=l.id
join tblclass c on c.classcode=l.classcode
join tblsubject s on s.sshortname=l.subject group by p.lessontopicid

Output

enter image description here

Apart from this problem if possible I want to compare the first date in date column with today's date and want to find out the records which fall today or after today's date for eg my first row has 27 August 2013,07 November 2013,05 May 2014 now I want to match 27 August 2013 with today's date

Community
  • 1
  • 1
rupinder18
  • 795
  • 1
  • 18
  • 43

1 Answers1

1

Q1) Try changing %m "Month, numeric (00-12)" to %M "Month name". Q2) GROUP_CONCAT ORDER BY

Community
  • 1
  • 1
Darren Lilley
  • 404
  • 5
  • 9
  • thanx it solved my incorrect datetime problem and I have already used order by.Please help me to solve the second part where I can match the first date with today's date – rupinder18 Jun 18 '14 at 06:24