Note: Tomorrow is February 29th. This blog post is dedicated to coming tomorrow – a special day :)
Q: “How can I find leap year in using SQL Server 2012?“
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))ENDGO
Q: “How can I find leap year in using SQL Server 2012?“
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))ENDGO
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.
Here is the article Pinal Dave has 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.
2 comments:
Incorrect Logic
There are so many people still think Leap Year is the event which is consistently happening at every four year and the way to find it is divide the year with 4 and if the remainder is 0. That year is leap year. Well, it is not correct.
Check out this excerpt from wikipedia page
http://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.”
If you use logic of divide by 4 and remainder is 0 to find leap year, you will may end up with inaccurate result. The correct way to identify the year is to figure out the days of February and if the count is 29, the year is for sure leap year.
=========================
Valid Alternate Solutions
=========================
1] IIF((@Year%4=0 AND @Year%100 != 0) OR @Year%400=0, 1,0)
2]
DECLARE @year INT
SET @year = 2012
IF (((@year % 4 = 0) AND (@year % 100 != 0)) OR (@year % 400 = 0))
PRINT ’1'
ELSE
print ’0'
3]
DECLARE @Year INT = 2012
SELECT ISDATE('2/29/' + CAST(@Year AS CHAR(4)))
4]Another approach would be to take one day off March 1st and see if it is 29.
Post a Comment