Need to select year of date/time field. 1

[brian1313]

I've been having problems with this for the past few days.

Here's my goal. One of my old music professors and I have a database of events that are going to be displayed by the semester and year of performance.

What I'd like to do is be able to populate a pulldown link menu like what I've just built with a series of "FALL, 2002," "SPRING, 2003," "SUMMER, 2003," etc. fields. The database has a date/time field and a semester field since the semester dates change year to year and I can't find a way to specify what events are in which semester by the date fields alone. I've tried a DISTINCT query and got it to work with a cheesy mockup, but it's not how we'd like to make it work and the DISTINCT query returns every record because almost all the dates are unique.

The way I see it, I should be able to select all records that turn up FALL and 2002 (subquery???), limit that list to one record, display FALL 2002 as a single link, and move on through the loop to SPRING 2003, SUMMER 2003, etc. The big questions for me are:[ul][li]How do I get just the year value from the date field?[/li][li]How do I limit the recordset to just one record?[/li][li]How do I set up the loop to start after, say 1998 and go ad nauseum?[/li][/ul]Sorry so long. Hope someone can help.

-b-

 

[r937]

select distinct
case
when month(event_date) between 1 and 3
then 'Winter '
when month(event_date) between 4 and 6
then 'Spring '
when month(event_date) between 7 and 9
then 'Summer '
else 'Fall '
end as event_semester
, year(event_date) as event_year
from events2

rudy

http://rudy.ca/

 

[object]

- Year(Date)
- <CFQUERY maxrows=&quot;1&quot;></CFQUERY>

 

[brian1313]

rudy,

will that work with MS Access? if rogto to mention what DB i was using...

thanks...
-b-

 

[r937]

with ms access you have to use IIF instead of CASE

select distinct
  IIF(month(event_date) between 1 and 3,'Winter ', 
  IIF(month(event_date) between 4 and 6,'Spring ', 
  IIF(month(event_date) between 7 and 9,'Summer ', 
        'Fall')))   as event_semester
 , year(event_date) as event_year
from events2

note how the IIF expressions have to be nested

if you need them sorted, modify the sql rather than trying to do it in your coldfusion code

rudy

 

[brian1313]

OK rudy, you get a second star for one thread, it's working perfectly!

And i even figured out how to list by semester then year... I wanted it listed like this
fall2001
spring 2002
summer2002
fall 2002, etc...

Thanks a bunch for all your help...

-b-