How to Get the Distinct Years from Sql Table having Dates in the Table

Question

0.00/5 (No votes)

See more:

I have a Table Containing Dates .
From this Table of Dates which Contains
dates from previous 5 years till Now.
I have the Requirement to Load a DropDownBox in Classic Asp
with the Years Available in the Table
I want to load the DropDownBox with Distinct years from the
Table in ascending order.the problem is I dont have Years in the table
whereas have the Transaction Dates Till now.
How can I achieve this.
I want the Sql Query to get All the Distinct Years in the
table from the Dates in the Table
Please Assist.

Posted 8-Jul-13 3:20am

Karwa_Vivek

Add a Solution

Comments

gvprabu 8-Jul-13 9:24am

Hi You need to show past 5 years in drop down right

2 solutions

Solution 2

Hi,

Try like below Query

SQL

IF NOT EXISTS(SELECT * FROM table_name)
BEGIN
	SELECT YEAR(GETDATE())-4 'YearList'
	UNION ALL
	SELECT YEAR(GETDATE())-3
	UNION ALL
	SELECT YEAR(GETDATE())-2
	UNION ALL
	SELECT YEAR(GETDATE())-1
	UNION ALL
	SELECT YEAR(GETDATE()) 
END
ELSE 
BEGIN
	SELECT TOP 5 T.YearList
	FROM (SELECT DISTINCT YEAR(DateColumn) 'YearList' FROM table_name ) T
	ORDER BY T.YearList ASC
END

Regards,
GVPrabu

Posted 8-Jul-13 3:27am

gvprabu

Updated 8-Jul-13 3:30am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Nirav Prabtani** · Accepted Answer · 2013-07-08T03:27:00

Solution 1

SQL

select distinct year(column) from TableName order by ASC

Posted 8-Jul-13 3:27am

Nirav Prabtani

v2

Comments

Karwa_Vivek 8-Jul-13 9:46am

select Distinct DATEPART (year, column) FY from TableName order by FY ASC
--thanks I used Like this