Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85

    Question Unanswered: SELECT DISTINCT to return only the YEARS in a date field?

    I have a table in my MS SQL 2000 database called News which has a field caled NewsDate. This is a standard Date field which stores the info in this format: 3/1/2001.

    I want to create a query that returns one row for each year that there is a story.

    Code:
    For example, if I had this data...
    3/1/2001, 6/27/2003. 9/17/2003, 1/1/2006, 4/5/2006
    
    the query would return this result:
    
    2001
    2003
    2006
    This is the query I've started with:

    Code:
    SELECT DISTINCT NewsDate FROM News  ORDER BY NewsDate DESC
    What modifier can I apply to the NewsDate field to extract JUST the year from the table? If this were ASP I would try something like Year(Date), but, of course, I can't do that here.

    Is this even possible? I've been looking up date functions, but haven't found anything that will work in a select statement. ANY and ALL advice will be greatly appreciated.

  2. #2
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85
    Heh. I figured it out on my own! Here's the code:

    Code:
    SELECT DISTINCT   Year(NewsDate)
    FROM News
    WHERE NewsIsInArchives = True
    ORDER BY Year(NewsDate) DESC

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •