Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 780
  • Last Modified:

Sql query for getting all years greater than 2003 and less than or equal to current year.

Hi Experts.

I need SQL query for finding, all years which are greater than 2003 and less than or equal to Current Year

ans. 2004,2005,2006,2007,2008,2009,2010,2011,2012,2013,2014

Note:  all the above years are not stored in any table of SQL Server.
0
Tapan Pattanaik
Asked:
Tapan Pattanaik
  • 4
  • 2
  • 2
  • +4
1 Solution
 
Phillip BurtonCommented:
Do you have any tables which are more than 2,000 rows in length?

Let's say you have the AdventureWorks2012 database. Person.Address is about 20,000 rows in length.

You can use this:


with mynums as (
SELECT ROW_NUMBER() over(order by addressiD) as myRow
from [AdventureWorks2012].[Person].[Address])
select myRow as numYear
from mynums
where myRow>2003 and myRow<year(getdate())
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
One (of many) possible Oracle ways:
select to_char(add_months(sysdate, -12 * (level - 1)), 'yyyy')
  from dual
connect by level <= 11;

Open in new window

0
 
Guru JiCommented:
Select DateFiled
  FROM YourTable
   Where YEAR(DateFiled)> 2003 and YEAR(DateFiled) !=YEAR(GETDATE())
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You didn't post your table structure so we don't know which fields we need to work out. So will leave here a global example and then you need to change it for your own solution:
SELEC *
FROM YourTableNameHere
WHERE YEAR(DateColumn) BETWEEN 2003 AND YEAR(GETDATE())

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Alexander, this question is in SQL Server area so your code won't work at all.
0
 
Guru JiCommented:
Sorry

Select DateFiled
  FROM YourTable
   Where YEAR(DateFiled)> 2003 and YEAR(DateFiled) <=YEAR(GETDATE())
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
@Vitor: I know, but the asker has also tagged the question with "Oracle" ;-)
0
 
Randy PooleCommented:
Select * from YourTable  Where YEAR(yourdate)> 2003 and YEAR(yourdate)<=YEAR(GETDATE())

Open in new window

0
 
Phillip BurtonCommented:
Please note the last sentence: "Note:  all the above years are not stored in any table of SQL Server."

Therefore, a simple Select statement will not work, as the numbers are not stored in any table.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Oh yes. Saw it now. Sorry Alexander.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
@Phillip, I think that only means the years as numbers aren't stored in any table. And how's that your solution returns years if you aren't working with dates?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
There's no way I know of to do this in a manner not hard-coded, but the below SQL Server Stored Procedure (I can't speak to Oracle) will return a list of years from 2003 to whatever the current year is, without the dependancy on any pre-existing table:

CREATE PROC year_list AS

CREATE TABLE #years (year_number int) 

Declare @yr int = 2003

WHILE @yr <= YEAR(GETDATE())
   begin
   INSERT INTO #years (year_number) VALUES(@yr) 
   SET @yr = @yr + 1
   end

-- RETURN SET
SELECT year_number FROM #years
GO

Open in new window

0
 
Tapan PattanaikSenior EngineerAuthor Commented:
Thanks to all experts and special thanks to  Jim Horn.

Note: Here there is no table storing the Years value.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now