SQL SERVER – Function: Is Function – SQL in Sixty Seconds #004 – Video

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.

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)

Database, SQL in Sixty Seconds, SQL Scripts
Previous Post
SQL SERVER – Detecting Leap Year in T-SQL using SQL Server 2012 – IIF, EOMONTH and CONCAT Function
Next Post
SQLAuthority News – The Best Quotes of “Who Wrote This?” Contest

Related Posts

9 Comments. Leave new

  • I guess this is the easier way to find it…

    CREATE FUNCTION dbo.IsLeapYear (@year INT)
    RETURNS bit
    Declare @Returnvalue bit
    IF (@year % 4 = 0)
    SET @Returnvalue = 1
    SET @Returnvalue = 0
    RETURN @Returnvalue

  • sainswor99insworth
    March 1, 2012 3:35 am

    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.

      • sainswor99insworth
        March 1, 2012 7:11 pm

        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)))

  • Dhiren Kumar Kaunar
    September 16, 2012 8:16 pm

    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.


Leave a ReplyCancel reply

Exit mobile version