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.

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:

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

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)

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.

“…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.”

— 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

Pinal Dave is a technology enthusiast and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and have written over 3800 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 14+ years of hands on experience he holds a Masters of Science degree and a number of database certifications. For any SQL Server Performance Tuning Issue send email at pinal @ sqlauthority.com .

Nupur Dave is a social media enthusiast and and an independent consultant.