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 (http://blog.sqlauthority.com)

About these ads

11 thoughts on “SQL SERVER – Function: Is Function – SQL in Sixty Seconds #004 – Video

  1. 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

    Like

    • 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.

      Like

      • 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.

        Like

  2. Pingback: SQL SERVER – Various Leap Year Logics « SQL Server Journey with SQL Authority

  3. Here is also another way to find whether a year is leap year or not

    Select ISDATE(’02-29-‘+ cast(‘2012′ as varchar(4)))

    Like

  4. 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.

    Like

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #018 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s