Monday, November 21, 2011

SQL Server - Check for Leap Year (Part 2)

And here is the second part to check for leap year. This uses the standard algorithm to check for leap year.
For more information about Leap Year: Leap Year on Wikipedia
 


-------------------------------------------------
-- Does the function already exist? 
-- if so... drop it.
-------------------------------------------------
IF OBJECT_ID(N'dbo.xIsLeapYear2',N'FN') IS NOT NULL
BEGIN
    print 'Function found... Dropping'
    DROP FUNCTION dbo.xIsLeapYear2
END

GO

CREATE FUNCTION xIsLeapYear2(@Year SmallInt)
RETURNS CHAR(3)
AS



-------------------------------------------------
--Function will Check to See if the year
--passed in is a leap year.
--
--This function uses an Algorithm to get the job done. 
--
--You pass in a Year and it returns 'Yes' or 'No'
-------------------------------------------------

BEGIN

    DECLARE @CheckYear Char(3)
   
    SET @CheckYear =
            (
              SELECT
                CASE
                    -- Is the Year Divisible by 400 with 0 remaining?
                    WHEN @Year%400 = 0 THEN 'YES'
                   
                    -- Is the Year Divisible by 100 with 0 remaining?
                    WHEN @Year%100 = 0 THEN 'NO'
                   
                    -- Is the Year Divisible by 4   with 0 remaining?
                    WHEN @Year%4   = 0 THEN 'YES'
                ELSE
                    'NO'
                END
            )


 

    RETURN @CheckYear

END

GO




-------------------------------------------------
--Now we will check to see if the code works
-------------------------------------------------


PRINT''
PRINT 'Using the algorithm operator to check leap year.'
PRINT '------------------------------------------------'
PRINT 'Is 1900 a Leap year?  ' + dbo.xIsLeapYear2(1900)
PRINT 'Is 2000 a Leap year?  ' + dbo.xIsLeapYear2(2000)
PRINT 'Is 2008 a Leap year?  ' + dbo.xIsLeapYear2(2008)
PRINT 'Is 2010 a Leap year?  ' + dbo.xIsLeapYear2(2010)





1 comment:

  1. The Casino Hotel Maryland - JH Marriott Spa & Casino
    Explore the 경상남도 출장샵 best places 경주 출장안마 to stay and play in 광주 출장안마 Maryland. Make your 사천 출장샵 stay at a top-rated resort and 구미 출장마사지 casino at Mohegan Sun one of the best locations

    ReplyDelete