Hello, I'm wondering if someone might be able to offer me a solution to the following problem that has so far stumped me. I'm trying to get the distinct months (and years) from a date field to display as a list in a view. For example, there might be a number of records stored with dates in the table 'headlines': name date record1 21-09-2008 record2 15-09-2008 record3 03-08-2008 record4 02-08-2008 record5 12-07-2008 etc.
From which I'd like to get all distinct years and months to display in a view something like what might be seen under the 'Archives' section of a blog page, like this: September 2008 August 2008 July 2008 etc.
I have tried to set the following sql statement in my controller to find the months using find_by_sql: @headlines_month = Headline.find_by_sql("SELECT distinct MONTHNAME(date) FROM headlines")
However, this errors out as:
PGError: ERROR: function monthname(date) does not exist at character 17 HINT: No function matches the given name and argument types. You may need to add explicit type casts. : SELECT distinct MONTHNAME(date) FROM headlines
It's monthname() which is what's causing it to error out as, if i remove monthname() there are no problems. I'm not sure if this is a problem of my limited understanding of that rails command or an error with my sql statement but I'm out of ideas so if anyone might have an idea for an effective solution - or can point me in the right direction - I'd very much appreciate it.
Thanks in advance. Best IRT