CODEWITHSUNDEEP
sql-serversql-server-2012
Przemek
Przemek

Reputation: 317

MS SQL 2012 Function returns no value

I'm probably missing something obvious but I can't find why does the below function return no value when tested with e.g. SELECT * FROM dbo.Age ('2016-03-01','2016-03-31'). It should return age in years months days format.

    CREATE FUNCTION [dbo].[Age] (@DateFrom DATETIME, @DateTo DATETIME)
RETURNS @Staz TABLE (Years INT, Months INT, Days INT)

AS
BEGIN
DECLARE @Years int, @Months int, @Days int
IF DAY(@DateFrom)=1 and MONTH(@DateFrom)=1 and DAY(@DateTo)=31 and MONTH(@DateTo)=12
 BEGIN
  IF YEAR(@DateFrom)=YEAR(@DateTo)
   SET @Years = @Years + 1
  ELSE
   SET @Years = @Years + (YEAR(@DateTo) - YEAR(@DateFrom) + 1)
 END
ELSE
 BEGIN
  IF MONTH(@DateFrom)=MONTH(@DateTo) and YEAR(@DateFrom)=YEAR(@DateTo)
   BEGIN
    IF DAY(@DateFrom)=1 and DAY(DATEADD(day,1,@DateTo))= 1
     SET @Months = @Months + 1
    ELSE
     BEGIN
      SET @Days =  @Days + Day(@DateTo) - Day(@DateFrom) + 1
     END
   END
  ELSE
  BEGIN
   IF  YEAR(@DateFrom)=YEAR(@DateTo)
    BEGIN
     SET @Months = @Months + (MONTH(@DateTo) - MONTH(@DateFrom) - 1)
    END
   ELSE
    BEGIN
     SET @Years = @Years + (YEAR(@DateTo) - YEAR(@DateFrom) - 1)
     SET @Months = @Months + (12 - Month(@DateFrom) + Month(@DateTo) -1)
                END
    IF DAY(@DateFrom)= 1
     SET @Months = @Months + 1
    ELSE
    IF DAY(@DateFrom)>1
     BEGIN
     DECLARE @TempData datetime
     SET @TempData = DATEADD(month,1,DATEADD(day,1-DAY(@DateFrom),@DateFrom))
     SET @Days = @Days + DATEDIFF(day,@DateFrom,@TempData)
     END
    IF DAY(DATEADD(day,1,@DateTo))<>1
     SET @Days = @Days + DAY(@DateTo)
      IF DAY(DATEADD(day,1,@DateTo))=1
     SET @Months = @Months+1
  END
 END

SET @Months = @Months + @Days/30
SET @Days = @Days%30
SET @Years = @Years + @Months/12
SET @Months = @Months%12

RETURN
END

Upvotes: 0

Views: 38

Answers (1)

SCFi
SCFi

Reputation: 522

You do not appear to be inserting into @Staz table at any place in the function

add an insert at the end into the table with the appropriate values and you should be getting your table back with values

Upvotes: 2

Related Questions