Friday, July 1, 2011

Display the List of Years Between the two dates ---- Procedure & Function


--------- List of Years PROCEDURE

CREATE PROCEDURE PROC_ListofYears (@SDate DATETIME ,@EDate DATETIME )
AS
BEGIN
DECLARE @I INT
DECLARE @J INT
DECLARE @TAB TABLE (YEARS DATETIME )
SET @I = 0
SELECT @J= DATEDIFF (YY,@SDate,@EDate)
WHILE (@I<=@J)
BEGIN
INSERT INTO @TAB SELECT DATEADD(YY,@I,@SDate)
SET @I = @I +1
END
SELECT DATEPART (YY,YEARS) FROM @TAB
END

EXEC PROC_LISTOFYEARS '2000-06-30 ','2011-06-30'

-----------List of Years Function

CREATE FUNCTION UDF_ListofYears (@SDate DATETIME ,@EDate DATETIME )
RETURNS @Years TABLE (Years DATETIME )
AS
BEGIN
DECLARE  @I INT
DECLARE  @J INT
SET @I = 0
SELECT @J = DATEDIFF(YY,@SDate,@EDate)
WHILE (@I<=@J)
BEGIN
INSERT INTO @Years SELECT DATEADD(YY,@I,@SDate)
SET @I = @I+1
END
RETURN
END

No comments:

Post a Comment