?
Solved

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

Posted on 2014-09-16
14
Medium Priority
?
741 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 14

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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 52

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 52

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 14

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 52

Expert Comment

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

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 66

Accepted Solution

by:
Jim Horn earned 2000 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 66

Expert Comment

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

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

718 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