[thelist] CF & SQL: Selecting unique months from date column

[Morgan Kelsey]

minh,

> Greeting,
> I have a column of dates in my database. I would like to display only each
> month available. I could select every date and write a loop to look at
every
> date then append to a list/array if the month does not exist... but that
> would be selecting a lot more records than I need. The first record in
that
> month may not be the first of the month, so I can't use that logic,
either.
>

if you only want the months, you can go:

<cfquery name="fetchMonths" datasource="ds">
SELECT
DISTINCT Month(your_date_field) AS themonth
FROM your_calendar_table
ORDER BY your_date_field
</cfquery>

<cfoutput query="fetchMonths">
#MonthAsString(themonth)#<br />
</cfoutput>


====================================
if you want events for each month, ditch the distinct, and group by the
months on output:

<cfquery name="fetchEvents" datasource="ds">
SELECT
Month(your_date_field) AS themonth
, your_date_field
, eventname
FROM your_calendar_table
ORDER BY your_date_field
</cfquery>

<cfoutput query="fetchEvents" group="themonth">
 #MonthAsString(themonth)#<br />
 <cfoutput >
  #your_date_field# #eventname#><br />
 </cfoutput>
</cfoutput>


hth,

nagrom
http://morgankelsey.com