In honor of today being February 29th I thought I would create a quick post to discuss how to create a user defined function to determine if any given year is a normal calendar year or a leap year. Though there is likely not much of a use for this in any real world scenario, aside from adding this logic to a corporate calendar table or some other date based calculator, I really just thought this would be a fun exercise.
The following script will create a user defined function in your database. The function will accept a single four digit year value and return a string stating whether the year specified is a leap year or a normal calendar year.
CREATE FUNCTION IsLeapYear(@year INT)
DECLARE @yearval INT,
SET @yearval = @year
SET @results = CASE
WHEN((@year % 4 = 0) AND (@year % 100 != 0)
OR (@year % 400 = 0))
THEN CAST(@year AS VARCHAR(4)) + ' is a leap year.'
ELSE CAST(@year AS VARCHAR(4)) + ' is a normal calendar year'
Note that the value being passed to the function is of datatype
INT. The results will be displayed as a string which requires that the value be converted using either
CAST. However, if you wanted to modify this script to use it to assist in creating a calendar table or some other calendar based calculation, you can change the datatype and have it output a 1 or 0 based on the result.
Once the function has been created you can easily call it using the following script. In this example I am calling it twice in order to demonstrate both results.
The results of executing this are displayed below:
As stated above, there are very limited uses for a script of this nature but if you’re just interested in how to create a basic user defined function and then call it, this may provide some useful insight.
Happy Leap Year 2016!