[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 868
  • 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 BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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 BurtonDirector, Practice Manager and Computing ConsultantCommented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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