sorting mysql query by month, year for date column in varchar type d-M-Y

If you follow the principles outlined here (using the ISO-8601 standard date format) your work will instantly get easier!
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

I see I overlooked the "mysql" part here.
so my "cast" part to get "ordered" results will not work for MySQL (only MS SQL)
and I do agree 200% that a varchar field is the wrong choice, in first place.

@angelIII: I'll see your 200% and raise you 200%

;-)

~Ray

hi angel

ur queries working fine except the 2nd one
 "ordered" by the proper month/year,
when using getting syntax error
right syntax to use near ''01-' + right(posted_on, 8) , 106) LIMIT 0, 30' at line 4

query : select right(posted_on, 8), count(*)
from listing
group by  right(posted_on, 8)
order by convert( datetime, '01-' + right(posted_on, 8) , 106)


any idea wats wrong

when i use this query

select right(posted_on, 8), count(*)
from listing
group by  right(posted_on, 8) order by  right(posted_on, 4)

i get... below result but months are not in right order...
right(posted_on, 8)      count(*)
Sep-2010      183
Nov-2010      13296
Oct-2010      15422
Dec-2010      220
Feb-2011      38310
Apr-2011      2
Jan-2011      23122
Jul-2011      20362
Jun-2011      17290
Nov-2011      1
Mar-2011      14269
Dec-2011      1
Sep-2011      2685
Aug-2011      32379
Oct-2011      29
May-2011      17443
Feb-2012      326
Jan-2012      55
Mar-2012      2428
Apr-2012      1328

@global_expert_advice: Please make this easier on yourself.

1. Use ALTER TABLE to add a new column that conforms to the MySQL DATETIME data type and the ISO-8601 standard.
2. SELECT the VARCHAR column that has your existing date values.
3. Use strtotime() and date('c') to reformat the date so that it complies with the standard.
4. UPDATE each row to insert the ISO-8601 standard date value into the new column.

This is a one-time action that will help you forever avoid wasted time on questions like this!  All your date selections and calculations will suddenly work the right way.

Best of luck with it, ~Ray

thanks will check and let u know..

@TomasHelgi:  i tried urs.. was giving null in place of months..

i made little change to urs

select STR_TO_DATE(right(posted_on, 8),  '%M-%Y' ) as month, count(*)
from listing
group by  month

This is now giving me perfectly... except one issue, the date is showing 00, as mysql puts 00 for invalid dates. and as i did right(posted_on,8) because of which date gone..
so any idea how to change the 00 of each month to last date of particular month.

month      count(*)
2010-09-00      183
2010-10-00      15422
2010-11-00      13296
2010-12-00      220
2011-01-00      23122
2011-02-00      38310
2011-03-00      14269

thanks

HI
Thanks i manage to do this by using below query

select
LAST_DAY(STR_TO_DATE(right(posted_on, 8), '%M-%Y')) as month,
count(*)
from listing
group by  month

Nice help
Thanks

What about next code:
right(posted_on, 4) = order by Year
left(right(posted_on, 8), 3) = Month

Order by right(posted_on, 4),
Case left(right(posted_on, 8), 3) 
	when 'Jan' then 1
	when 'Feb' then 2
	when 'Mar' then 3
	when 'Apr' then 4
	when 'May' then 5
	when 'Jun' then 6
	when 'Jul' then 7
	when 'Aug' then 8
	when 'Sep' then 9
	when 'Oct' then 10
	when 'Nov' then 11
	when 'Dec' then 12
	Else 99 end

Select allOpen in new window