SQL SERVER – Detecting Leap Year in T-SQL using SQL Server 2012 – IIF, EOMONTH and CONCAT Function

SQL SERVER - Detecting Leap Year in T-SQL using SQL Server 2012 - IIF, EOMONTH and CONCAT Function leap-year

Note: Tomorrow is February 29th. This blog post is dedicated to coming tomorrow – a special day :)

Subu: “How can I find leap year in using SQL Server 2012?

Pinal: “Are you asking me how to year 2012 is leap year using T-SQL – search online and you will find many example of the same.”

Subu: “No. I am asking – How can I find leap year in using SQL Server 2012?

Solarwinds

Pinal: “Oh so you are asking – How can I find leap year in using SQL Server 2012?

Subu: “Yeah – How can I find leap year in using SQL Server 2012?

Pinal: “Let me do that for you – How can you find leap year in using SQL Server 2012?

Indeed a fun conversation. Honestly, only reason I pasted our conversation here is – it was fun. What he was asking is that how to do it using new functions introduced in SQL Server 2012. Here is the article I have written which introduces all the new functions in SQL Server 2012 Summary of All the Analytic Functions – MSDN and SQLAuthority and 14 New Functions – A Quick Guide.

There are many functions written to figure out to figure out if any year is Leap Year or not. The same I have written using T-SQL function over here.

CREATE FUNCTION dbo.IsLeapYear (@year INT)
RETURNS INT
AS
BEGIN
RETURN(IIF(DATEPART(dd,(EOMONTH(CONCAT(@year,'0201')))) = 29,1,0))
END
GO


What I really like is that I was able to use three newly introduced function in SQL Server 2012 in above script. You can read more about them here. IIF, EOMONTH and CONCAT.

You can validate above query by running following script.

SELECT dbo.IsLeapYear('2011') 'IsLeapYear';
SELECT dbo.IsLeapYear('2012') 'IsLeapYear';
GO


You will get result 1 if the year is leap year and 0 if year is not leap year.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – Identifying Guest User using Policy Based Management
Next Post
SQL SERVER – Function: Is Function – SQL in Sixty Seconds #004 – Video

Related Posts

28 Comments. Leave new

  • Does it only work with 4 digits year ?

    Reply
  • Hi pinal
    If i am using Sql Server 2008 then How can i find it.

    Reply
  • Got an error as : ‘CONCAT’ is not a recognized built-in function name.

    Reply
  • Hi Dear Pinal
    can you please seperate “Sql Server 2012” tips and tricks in a tag?finding just Sql Server 2012 tips is difficult in you very good blog.
    thanks

    Reply
  • Here are different methods to find out the leap year

    Reply
  • Hi Pinal,

    I just try for other simple method to find leap year… Please check the below query.

    /*

    Declare
    @FromYear as int
    set @FromYear=’2012′

    select
    case when
    ((@FromYear/4.0) /cast (@FromYear/4 as varchar(5)))= 1 then ‘Leap Year’
    else ‘Not Leap Year’ end

    */

    Reply
    • Hi Ramesh

      The formula you have written is wrong. You have to check the century year as well as year divisible by 400.

      Thanks
      Sanjay

      Reply
  • Very interesting!!! thanks Pinal.

    Is it possible to work on SQl 2008, kindly suggest

    Reply
  • I used to write some thing similar to this in my javascript date validations. Since I dont have SQL2012 currently, I use ‘if’. Using iif would make the code shorter and I guess this uses less resources.

    DECLARE @year int
    SET @year = 2012
    if (((@year % 4 = 0) AND (@year % 100 != 0)) OR (@year % 400 = 0))
    print ‘1’
    ELSE
    print ‘0’

    Reply
  • I know you wanted to showcase the SQL2012 functions, but another solution that doesn’t depend on SQL2012 and is much shorter and easier to understand is to just use the IsDate() function as in this example:

    declare @Year int = 2012
    select isdate(‘2/29/’ + cast(@Year as char(4)))

    Note that besides being very simple to read and understand, this works correctly for null, 2 digit years and all of the whole centuries too. Unlike some of the solutions I’ve seen, this one does not cause a divide-by-zero error when the year = 0. Most of this SQL is test cases. The real code is all on one short line.

    ;with TestData (Y) as (
    select cast(null as int)
    union select 0
    union select 1
    union select 2
    union select 3
    union select 4
    union select 5
    union select 48
    union select 52
    union select 1900
    union select 1996
    union select 1999
    union select 2000
    union select 2001
    union select 2004
    union select 2011
    union select 2012
    union select 2013
    union select 2100
    union select 987654321
    )
    select Y as [Year],
    /*
    Careful – According to SQL BOL,
    ISDATE is deterministic only if you use it with the CONVERT function,
    if the CONVERT style parameter is specified, and style is not equal to 0, 100, 9, or 109.
    The return value of ISDATE depends on the settings set by
    SET DATEFORMAT, SET LANGUAGE and default language option.
    The date string may need to be formatted differently depending on your SQL server configuration.
    This next column is the one that determines if the specified year is a leap year.
    */
    isdate(‘2/29/’ + cast(Y as char(4))) as IsLeapYear,
    — This next column is unnecessary.
    — It just shows that this works with 1 & 2 digit years
    case when isdate(‘2/29/’ + cast(Y as varchar(4))) = 1
    then cast(‘2/29/’ + cast(Y as varchar(4)) as date)
    else null
    end as LeapDay
    from TestData
    order by Y

    Reply
  • Thanks for posting even more info on SQL Server 2012 features and please keep them coming as this is a great place to learn.

    And well done to David for your excellent 2008 version. I really like what you did there. Whenever I see a bit of 2012 code I always wonder how to acheive the same thing in 2008 and its usually not too hard to work out but it is usually longer and slightly more difficult to follow. Yours isn’t in-fact it proves that sometimes new features are just there for the more obvious issues and not always better (like CONCAT – which is pretty simple to mimick in 2008).

    (incidentally – Another approach would be to take one day off March 1st and see if it is 29)

    Thanks to both of you.

    Dave (are we collectively all SQL Dave?)

    Reply
  • Just divide year by 4. If the remainder of the division returns 0, its leap year.

    select 2012 % 4

    returns 0

    simple

    Reply
  • create function fncLeapYear
    (@date smalldatetime) returns bit
    as
    begin
    declare @r bit
    set @r = case year(@date)%4 when 0 then 1 else 0 end

    return @r
    end

    select dbo.fncLeapYear (getdate())

    Reply
  • fbncs: your function is incorrect for adjustment years.

    Admittedly the next adjustment year is 2100 so many of us will not actually care about this but fact remains that we cannot just check if the year is divisible by 4.

    This is why MS saw fit to put the function in to SQL so that developers do not have to worry about it. I think we should avoid re-inventing the wheel and check a date against what Microsoft has done for us (even in 2008 the functionality exists as it knows if there are 29 days if you ask it that question directly) hence I think it works to take one off 1st march and test if its 29 in SQL Server 2008 and use the function Pinal spoke of for 2012.

    Personally I would always recommend using a date (calendar) table in your database ratherthan a date calculation as you allow not oly for leap years but also all other special dates, company financial year etc.

    Check out this excerpt from wikipedia page

    https://en.wikipedia.org/wiki/Leap_year

    “most years that are evenly divisible by 4 are leap years…”

    “…Some exceptions to this rule are required since the duration of a solar year is slightly less than 365.25 days. Years that are evenly divisible by 100 are not leap years, unless they are also evenly divisible by 400, in which case they are leap years. For example, 1600 and 2000 were leap years, but 1700, 1800 and 1900 were not. Similarly, 2100, 2200, 2300, 2500, 2600, 2700, 2900 and 3000 will not be leap years, but 2400 and 2800 will be.”

    Reply
  • JUST USE ISDATE() FUNCTION like:-

    CREATE FUNCTION dbo.IsLeapYear (@year INT)
    RETURNS INT
    AS
    BEGIN
    RETURN ISDATE(’02/29/’+ Cast(@year AS varchar(4)))
    END

    GO
    SELECT dbo.IsLeapYear(2012)

    Reply
  • Nawnit Kumar
    June 4, 2014 11:56 am

    Hi Pinal,
    I’m facing one problem from last 5 days. I need your help. I’m creating sp to calculate Gratuity of employee, but I’m unsle to calculate the @DayCount due to leap year. Can you please see the below stored procedure,
    ALTER PROCEDURE [Hrms].[Usp_CalculateGratuity]
    (
    @GratuityDate Datetime,
    @EmployeeID varchar(20),
    –@EMPLOYEE_NUMBER VARCHAR(20),
    @Gratuity INT OUTPUT
    )
    As
    BEGIN

    — Get salary drawn
    DECLARE @LastSalary DECIMAL(10,4)
    SELECT @LastSalary = BasicPay from ALLSec.dbo.tblAllSecLeaveSync where EmployeeID=@EmployeeID
    — Get First working date
    DECLARE @HireDate DATETIME

    SELECT TOP 1 @HireDate = First_Hire_date FROM Hrms.dbo.Hrms_Employee_Detail where EMPLOYEE_NUMBER = @EmployeeID
    ORDER BY Hrms_Employee_Detail.First_Hire_date DESC

    — Get last working date
    DECLARE @LWD DATETIME
    SELECT @LWD= [Approved LWD] FROM hrms.tblEmployeeDetails WHERE EmployeeNumber=@EmployeeID

    — Get total completed years
    DECLARE @CompletedYears INT
    SELECT @CompletedYears = DATEDIFF(YEAR,@HireDate,@LWD)

    — Calculate days in years
    DECLARE @DayCount INT
    SET @DayCount = @CompletedYears*365

    — Get total days
    DECLARE @CompletedDays INT
    SET @CompletedDays = DATEDIFF(DAY,@HireDate,@LWD) – @DayCount

    — Get total Months
    DECLARE @TotalMonths INT
    SET @TotalMonths = (@CompletedYears * 12)- DATEDIFF(MONTH,@HireDate,@LWD)

    — when number of years >= 4 years and days >= 239 days then return calculated Gratuity otherwise Gratuity = 0
    IF(@CompletedYears >= 4)
    IF ((@CompletedYears = 4) AND (@CompletedDays >= 239))
    BEGIN
    — If months >= 6 then total years should be increment by 1
    — Need not this condition here
    IF (@TotalMonths >= 6)
    BEGIN
    SET @CompletedYears = @CompletedYears + 1
    END
    — Finally calculate the Gratuity of employee
    SET @Gratuity = 15/26 * @LastSalary * @CompletedYears
    END
    — If completed years > 4
    ELSE
    BEGIN
    — If months >= 6 then total years should be increment by 1
    IF (@TotalMonths >= 6)
    BEGIN
    SET @CompletedYears = @CompletedYears + 1
    END
    — Finally calculate the Gratuity of employee
    SET @Gratuity = 15/26 * @LastSalary * @CompletedYears
    END
    ELSE
    BEGIN
    SET @Gratuity = 0
    END
    — Get Calculated Gratuity
    SELECT @Gratuity
    END

    Reply
  • is there any reason to mention in concat ‘0201’ can u explain that

    Reply
  • Hi Pinal, Does leap year effect SQL servers in any way do we have to take any precautions a head..

    Thanks In Advance

    Reply
  • This works for both 2/4 digit years: @Year/4.0 LIKE ‘%.0%’

    Samples:

    DECLARE @Year2 INT = 00

    SELECT
    CASE WHEN @Year2/4.0 LIKE ‘%.0%’ THEN ‘LEAP’ ELSE ‘NORMAL’ END

    DECLARE @Year4 INT = 2000

    SELECT
    CASE WHEN @Year4/4.0 LIKE ‘%.0%’ THEN ‘LEAP’ ELSE ‘NORMAL’ END

    Reply
  • I just want to get end quarter date list between two dates where two dates are not constant
    The output should be in given below format:
    start date End date
    20160701 20161231
    20160930 20170331

    output :
    start date End date End quarter date
    20160701 20161231 20160930
    20160701 20161231 20161231

    like wise .I need it

    Please help !

    Reply
  • Pinal, I want to show the date of yesterday from last year based on today’s date. So 2017 is not a leap year but 2016 was, that being said when we reach 3/1/2017 my calendar returns 2/28/2016 when it should be 2/28/2016. Here is the code i currently have dateadd(yy,-1,dateadd(d,-1,DC.DateKey)). DC is my DimCalendar from my Data warehouse and datekey is the date.

    Reply

Leave a Reply

Menu