Post reply

Query to retrieve years till date starting from year 2004

  • October 17, 2008 at 6:35 am

    #383136

    I want to retrieve all the years from "2004" to current year by writing a query in SQL.:hehe:

    Thanks!

  • October 17, 2008 at 6:43 am

    #886313

    it's pretty simple...you'd use a BETWEEN statemnt for 2004 AND YEAR(GETDATE())...but you need to select it from a table, right?

    once again, Jeff Moden's Tally Table would be the ideal tool to use(is there anything a Tally table can't do?)

    --copied shamelessly from a Jeff Moden Example

    --Now, before we get to the solution, we need to make a well indexed table of sequential numbers. These "Tally" or "Numbers" tables are very powerful and can help do things in SQL Server 2000 as if we were using ROWNUM from SQL Server 2005. You should make a permanent Tally table as follows... yes, this is part of the solution for this and many other "impossible" tasks...

    --===== Create and populate the Tally table on the fly

    SELECT TOP 11000 --equates to more than 30 years of dates

    IDENTITY(INT,1,1) AS N

    INTO dbo.Tally

    FROM Master.dbo.SysColumns sc1,

    Master.dbo.SysColumns sc2

    --===== Add a Primary Key to maximize performance

    ALTER TABLE dbo.Tally

    ADD CONSTRAINT PK_Tally_N

    PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

    select N as year from dbo.tally where N between 2004 and year(getdate() )

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply