开发者

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'
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜