How to SELECT DISTINCT Year on a DateTime Field 1

[JohnBates]

hi ASP'ers,

My SQL Server 7 table has hundreds of date values. I want to select just the unique (Distinct) year values. So I should get a recordset containing 3 or so records: 2000, 001, 2002

______________________________________________________
This is the SQL statement I am trying to use in the ASP.

It gives error: Invalid col name YEAR

sql = "SELECT DISTINCT year(DateClosed) FROM LoadHist ORDER BY year"
______________________________________________________

(I thought this would work because I have used
sql = "SELECT Carrier, ItemCount FROM LoadHist WHERE year(DateClosed) = '" & intyr & "' AND month(DateClosed) = '" & intmo & "'" to select just on the year portion of a date and it worked)


As always, Thanks. John

 

[Guest_imported]

Try this:

SELECT DISTINCT year(DateClosed) FROM LoadHist
ORDER BY year(DateClosed)

You forgot to include the DateClosed column name with the year() SQL function.

Wally

 

[JohnBates]

Thanks WallyWerner,

I tried it....

sql = "SELECT DISTINCT year(DateClosed) FROM LoadHist ORDER BY year(DateClosed)"

Got an "ADODB.Recordset error Item cannot be found in the collection"

John

 

[JuanitaC]

Try assigning it a name, then ordering by that:

sql = "SELECT DISTINCT year(DateClosed) AS Closed_Year FROM LoadHist ORDER BY Closed_Year"

 

[JohnBates]

Thanks JuanitaC !

Your tip worked perfectly.

John

Do I get a Star for submitting the most questions within a 24 hour period? [tongue}