Saturday, June 28, 2008

How to find-out a Leap Year in SQL Server?

The only solution is to check the 29days of Feb for the year. Also, there are some rules which tells a year as 'Leap year'

1. It should divisible by 4
2. It should have 29days fo February

To do achieve the same I prefer inbuilt function 'datepart', may be there are many other ways to achieve the same.

But I prefer following one :

Create Function dbo.fnCheckLeapYear (@year int)
returns char(3) --This will retun Yes-if Leap year, No-If doesn't
return(Select Case datepart(mm, dateadd(dd, 1, cast((cast(@year as varchar(4)) + '0228') as datetime)))
when 2 then 'Yes'
else 'No'

The above functiona fnCheckLeapYear accepts one parameter which is year, one wants to know?

Example:Some example to check this function
1.Select dbo.fnCheckLeapYear(1986) as 'IsLeapYear?'
2.Select dbo.fnCheckLeapYear(2000) as 'IsLeapYear?'
3.Select dbo.fnCheckLeapYear(2013) as 'IsLeapYear?'
4.Select dbo.fnCheckLeapYear(2020) as 'IsLeapYear?'

Just copy and paste above function in SQL Query Analyzer and try the above examples.

No comments:

Post a Comment