Finding Leap Year in SQL in a different method

Usually to know whether the given year is Leap/Not a leap we will divide the given year by 4.

If the remainder is 0 its Leap. Otherwise it is not a leap year.  

But this leads to a wrong result in some scenario. Assume that given year is 2300. It can be divided by 4 with 0 as remainder. But 2300 is not a leap year.

So we can use to the different methods to get the desired result. Given one of the method i have used.

DECLARE @YEAR INT

SET @YEAR = 2300

SELECT @YEAR AS‘YEAR’, CASE

WHEN

DATEDIFF(DAY,

’01/FEB/’+CONVERT(VARCHAR,@YEAR),

’01/MAR/’+CONVERT(VARCHAR,@YEAR))

=29

THEN

‘Is a Leap Year’

ELSE

‘Is not a Leap Year’

END

AS RESULT

In the above example, @year acts as the input parameter which get “Year” as input. For the given Year, i was finding the day difference between Feb 01 and March 01. If it is 29, It is a Leap year Otherwise It is not. It will work under all scenario without any issues.

About these ads

About Diwakar

MCA Graduate, .Net Developer

Posted on July 26, 2012, in SQL. Bookmark the permalink. 3 Comments.

  1. I find more blog posts of you on SQL Server jus’ like Pinal Dave – http://blog.sqlauthority.com does. Great to archive these kinda posts da…keep blogging ;-)

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: