SQL Scalar UDF to get number of days in Year
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_IDENTI开发者_如何学JAVAFIER 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
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 @RetVal
WHEN 1 THEN 366
ELSE 365
End
Return @Result
END
Modified version of the above answer :
DECLARE @year INT,
@DaysInYear INT
SET @year = 2011
SELECT @DaysInYear = CASE DATEPART(mm, DATEADD(dd, 1, CAST((CAST(@year AS VARCHAR(4)) + '0228') AS DATETIME)))
WHEN 2 THEN 366 ELSE 365 END
SELECT @DaysInYear 'DaysInYear'
精彩评论