Date Manipulation - Selecting Years from Database

[Arielle]

I'm trying to build some custome filters for a document reader and one of the fields that I have is the document date (docdate). The customer wants to be able to filter the documents by year and month. For the purpose of the dropdown I wanted to select the distinct years from the docdate column.

<cfquery name="yearSelect" datasource="#request.dsn#">
SELECT DISTINCT DATEPART(docdate,"yyyy") AS YEARRELEASED FROM fileContent WHERE archivedFile = 0
</cfquery>
 

This is of course...incorrect.

I've also tried DATEPART(yyyy,docdate) and YEAR(docdate) and they don't work either. I'm a wee bit stumped.

[improvcornartist]

What kind of database are you using?

[Shift4SMS]

For MSSQL YEAR(docdate) should work provided that docdate is a datetime or smalldatetime field:

select distinct year(docdate) from filecontent
 

If this does not help, you may want to repost your question in one of the sql platform forums as this is more a sql question than a coldfusion question.