Today is February 29th. An unique date which we only get to observe once every four year. Year 2012 is leap year and SQL Server 2012 is also releasing this year. Yesterday I wrote an article where we have seen observed how using four different function we can create another function which can accurately validate if any year is leap year or not. We will use three functions newly introduced in SQL Server 2012 and demonstrate how we can find if any year is leap year or not.
This function uses three of the SQL Server 2012 functions – IIF, EOMONTH and CONCAT. When I wrote this function, this is the sortest function I ever wrote to find out leap year. Please watch the video and let me know if any shorter function can be written to find leap year.
[youtube=http://www.youtube.com/watch?v=tvrFKtUSl5c]
More on Leap Yer:
Detecting Leap Year in T-SQL using SQL Server 2012 – IIF, EOMONTH and CONCAT Function
Date and Time Functions – EOMONTH() – A Quick Introduction
Script/Function to Find Last Day of Month
I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.
Reference: Pinal Dave (https://blog.sqlauthority.com)
9 Comments. Leave new
I guess this is the easier way to find it…
CREATE FUNCTION dbo.IsLeapYear (@year INT)
RETURNS bit
AS
BEGIN
Declare @Returnvalue bit
IF (@year % 4 = 0)
SET @Returnvalue = 1
ELSE
SET @Returnvalue = 0
RETURN @Returnvalue
END
GO
Nice introduction, Pinal, but the shortest form I can think of revolves around:
IIF(@year%4=0, 1, 0)
No need to check for February 29; just check to see if the year is evenly divisible by 4.
This rule is not fully correct every time.
any year which is divisible by 4 and also divisible by 100 but not divisible by 400 is not leap year (even if it is evenly divisible by 4)
for example year 1900 was not a leap year, however year 2000 was a leap year.
Hmm, didn’t realize that; learn something new every day :). I guess you could do
IIF((@Year%4=0 AND @Year%100 0) OR @Year%400=0, 1,0)
but that’s probably not any shorter than using the CONCAT and EOMONTH() method.
Here is also another way to find whether a year is leap year or not
Select ISDATE(’02-29-‘+ cast(‘2012’ as varchar(4)))
Isdate() is not reliable
select isdate(‘2006’)
Hi Pinal,
I have sql server 2008 and 2012
and below is my sql server 2012 configuration detail.
Microsoft SQL Server Management Studio 11.0.2100.60
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 5.0 6.0
Microsoft Internet Explorer 9.0.8112.16421
Microsoft .NET Framework 4.0.30319.17626
Operating System 6.1.7601
I am getting error messages like given below , when we are tr
‘EOMONTH’ is not a recognized built-in function name.
‘CONCAT’ is not a recognized built-in function name.trying to run the above example.
Can you please help me to resolve this issue.