CODEWITHSUNDEEP
t-sql
user371819
user371819

Reputation: 105

how to formulate simple tsql year query?

I want to write a t-sql query which will simply list all of the years from 2005 to present. But i want this automated so this list will still be current next year. I know I could just hard code years with union. But how do I develop a t-sql query which will automate this?

Upvotes: 1

Views: 217

Answers (4)

t-clausen.dk
t-clausen.dk

Reputation: 44346

I would try this way

WITH cte(YEAR)
AS (
SELECT 2005 YEAR
UNION ALL
SELECT CH.YEAR +1
FROM cte ch    
WHERE YEAR <= YEAR(GETDATE())
)
SELECT * FROM CTE

result

YEAR
----
2005
2006
2007
2008
2009
2010
2011
2012

Upvotes: 2

NakedBrunch
NakedBrunch

Reputation: 49423

You can do this using Common Table Expressions (CTEs) and it doesn't require you to create a temporary table:

DECLARE @TheYear as date
SET @TheYear = '1/1/2005'

;WITH DateIntervalsCTE AS
(
    SELECT 0 i, @TheYear AS [Year]
    UNION ALL
    SELECT i + 1, DATEADD(year, i + 1, @TheYear )
    FROM DateIntervalsCTE 
    WHERE YEAR(DATEADD(year, i, @TheYear )) <= YEAR(GETDATE())
)
SELECT [Year] FROM DateIntervalsCTE

Upvotes: 2

AdaTheDev
AdaTheDev

Reputation: 147374

I'd recommend a tally table approach - basically just create a table (called "Tally"/"Numbers" as you wish) with a single column and populate with e.g. numbers 0 to 1000 and put a CLUSTERED index on the column.

You can then use it like:

SELECT 2005 + Num AS Yr
FROM Tally
WHERE Num <= YEAR(GETDATE()) - 2005

Tally tables can come in very handy for all kinds of usages, hence my suggestion to use one as it could help other scenarios.

Many articles on tally tables out there, here's just one:
http://www.sqlservercentral.com/articles/T-SQL/62867/

Upvotes: 4

The Evil Greebo
The Evil Greebo

Reputation: 7138

A crude but effective simple method

DECLARE @I INT
CREATE TABLE #years (TheYear datetime)

SET @I = 0 
WHILE @I <= DATEDIFF(year, '1/1/2005', GETDATE()) 
BEGIN
    INSERT INTO #years VALUES (DATEADD(year, @i, '1/1/2005'))
    SET @I = @I + 1
END

SELECT YEAR(TheYear) FROM #years
DROP TABLE #years

Encapsulate that in a function with a table type return and you should be able to join against that in future queries.

Upvotes: 1

Related Questions