0

So I've gone around the houses a bit with this and not got it to work...

I have a SELECT query which contains a VARCHAR column (alloc_mth) with dates in the MMM-YY format (e.g. Jan-15) which I want to be able to ORDER BY in my query. In order to do so, I need to convert to a recognised date format. I've been following the instructions here but I'm struggling to get it to work.

My SELECT query is as follows...

SELECT `alloc_mth`, SUM(`line_cleared`), SUM(`line_count`), (SUM(`line_cleared`)/SUM(`line_count`))*100 as `Fill` 
FROM `allocHist` 
GROUP BY 1
ORDER BY CAST('1.' + `alloc_mth` AS DATETIME) ASC;

The end result of this is that the grouping and sum works fine but the date format and sorting doesn't... enter image description here

Should this work or am I missing something?

Community
  • 1
  • 1
spcurtis81
  • 175
  • 2
  • 4
  • 14
  • 1
    http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date – Mihai Nov 10 '15 at 20:12
  • You're not using periods to delimit your dates. You're using dashes. Try `CAST('1-' + alloc_mth AS DATETIME)` – Bacon Bits Nov 10 '15 at 20:15

1 Answers1

2

With the expression,

'1.' + `alloc_mth`

You are performing addition on strings, which in mysql will evaluate to 1. The way to concatenate strings is with concat(str1, str2). However, that should be unnecessary in this case.

You want to convert to a date with str_to_date.

order by str_to_date(alloc_mth,'%b-%YY')
JRD
  • 1,957
  • 1
  • 12
  • 17