Select Distinct Year from Date

I would like to do a select distinct in a dropdown menu from a date field, but return only the Year, not the full date. The attached code returns all dates once, but I'd like returned only the year.
Thanks for your help.
<%
sql="SELECT distinct(OccDate) FROM Compliments"
Set rsYears = conn.Execute(sql)
%>

<select name="myYear">
<option></option>
<%
Do While Not rsYears.EOF
Response.Write "<option>"& rsYears("OccDate") &"</option>"
rsYears.MoveNext
Loop
Response.Write "</select>"
%>

Open in new window

romsomAsked:
Who is Participating?
 
chaauConnect With a Mentor Commented:
You need to extract Year from the date. Use this SQL syntax:
<%
sql="SELECT distinct DATEPART(year, OccDate) AS OccYear FROM Compliments"
Set rsYears = conn.Execute(sql)
%>

<select name="myYear">
<option></option>
<%
Do While Not rsYears.EOF
Response.Write "<option>"& rsYears("OccYear") &"</option>"
rsYears.MoveNext
Loop
Response.Write "</select>"
%>

Open in new window

0
 
romsomAuthor Commented:
This is perfect, thank you very much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.