SQL Scalar UDF to get number of days in Year

Question

I writing code to determine how many days in a year. I am trying to keep it really simple. I found code that I think is very clean to determine a leap year. I am passing the inputted date using DATEPART(Y,@Year) to the leap year program and some how am not getting the correct results so I has to be in my SQL code to process the input date as the correct bit is returned.

Here is the code for the Leap Year:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[FN_Is_Leap_Year]
(
 -- the parameters for the function here

        @year int
)

RETURNS BIT
AS
BEGIN
RETURN (select case datepart(mm, dateadd(dd, 1, cast((cast(@year as varchar(4)) + '0228') as datetime)))
WHEN 2 THEN 1
ELSE 0  END)

END

Here is the code I wrote to process the input date & get the # days in a year:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[FN_Get_Days_In_Year]
(
    @InputDT    varchar(10)
)

RETURNS int
AS
BEGIN

DECLARE  @Result  int,
         @Year    int   


Set @Result = 
 CASE
    WHEN dbo.FN_Is_Leap_Year(Datepart(yyyy,@Year)) = 0 Then 365
    WHEN dbo.FN_Is_Leap_Year(Datepart(yyyy,@Year)) = 1 Then 366
 END


        RETURN @Result
    END

Answer 1

In the code you've posted for FN_Get_Days_In_Year you don't seem to be assigning a value to @Year anywhere.

Answer 2

Got it working!!

GO ALTER FUNCTION [dbo].[FN_Get_Days_In_Year] ( @InputDT int )

RETURNS varchar(3) AS BEGIN

Declare @Year int,
@RetVal bit, @Result varchar(3)

Set @Year = datepart(yy, @InputDT) Set @RetVal = dbo.FN_Is_Leap_Year(Datepart(yy,'2012'))
Set @Result= Case When @RetVal = 1 then 366 else 365 End

Return @Result

END