Solved

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

Posted on 2014-09-16
14
595 Views
Last Modified: 2014-09-16
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
Comment
Question by:Tapan Pattanaik
  • 4
  • 2
  • 2
  • +4
14 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40325309
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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40325325
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
 
LVL 11

Expert Comment

by:Guru Ji
ID: 40325326
Select DateFiled
  FROM YourTable
   Where YEAR(DateFiled)> 2003 and YEAR(DateFiled) !=YEAR(GETDATE())
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40325327
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
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40325330
Alexander, this question is in SQL Server area so your code won't work at all.
0
 
LVL 11

Expert Comment

by:Guru Ji
ID: 40325331
Sorry

Select DateFiled
  FROM YourTable
   Where YEAR(DateFiled)> 2003 and YEAR(DateFiled) <=YEAR(GETDATE())
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40325332
@Vitor: I know, but the asker has also tagged the question with "Oracle" ;-)
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40325335
Select * from YourTable  Where YEAR(yourdate)> 2003 and YEAR(yourdate)<=YEAR(GETDATE())

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40325338
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
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40325339
Oh yes. Saw it now. Sorry Alexander.
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40325348
@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
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40325430
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
 
LVL 21

Author Closing Comment

by:Tapan Pattanaik
ID: 40325481
Thanks to all experts and special thanks to  Jim Horn.

Note: Here there is no table storing the Years value.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40325530
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
t-sql Joining Issue 10 40
Problem to refer to value 8 49
Expression Evaluater 3 25
Alternative of IN Clause in SQL Server 3 21
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question