Get Year

Posted - 01/08/2007 : 12:19:15

---

Hi,
In my report i have a parameter which displays the Year like this:2000,2001,..,2006.
When it is '1/1/2007' 2007 is automatically need to be add to Year parameter.This should automatically populate with each new year.
How to write the code for achieving this.

Thanks in advance

This 'should' provide the year for you.

SELECT year( getdate() )

|||

Hi,

I did this with this query:

Create Procedure rptCaseDetail_GetYear

as

Begin

Declare @.Year int

Set @.year=2000

Declare @.Current int

SET @.Current=Year(getdate())

declare @.jyear table

(

Year int

)

While (@.year<=@.Current)

Begin

insert into @.jyear Values( @.year)

SET @.Year=@.year+1

End

select * from @.Jyear order by year desc

End

|||I would create a function as it it easier to handle, joinable and more reusable:

ALTER FUNCTION dbo.NextYears

(

@.StartingYear SMALLINT,

@.AmountOfYears SMALLINT

)

RETURNS @.NextYears TABLE

(

TheYear SMALLINT

)

AS

BEGIN

WHILE @.AmountOfYears >= 0

BEGIN

INSERT INTO @.NextYears

SELECT @.StartingYear + @.AmountOfYears

SET @.AmountOfYears = @.AmountOfYears -1

END

RETURN

END

HTh, Jens K. Suessmeyer.

http://www.sqlserver2005.de