Problem selecting year of date/time field... (long)

[brian1313]

I mistakenly posted this in the ColdFusion forum before i really realized that y problem may be in the query.

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 (this is done... thanks object!), display FALL 2002 as a single link, and move on through the loop to SPRING 2003, SUMMER 2003, etc. I'm not too worried about the CF code, that's coming along just fine.

The big question for me is how do I get just the year value from the date field?

This has been suggested, but I think it is in SQL server syntax, because I can't get it to work.

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

Thanks in advance for any help.

-b-

 

[r937]

i've posted the access equivalent in thread232-370298

rudy