Checking for Leap Year - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Checking for Leap Year

Author

Topic

rauof_thameem
Starting Member

31 Posts

Posted - 2007-05-15 : 23:51:59

Hi..,

How do i can check for leap year in date

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-16 : 00:01:28

what is the input like ? just the year ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-16 : 00:02:10

Wiki has a good explanation on this http://en.wikipedia.org/wiki/Leap_year

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-16 : 00:27:06

[code]
declare @d datetime
set @d='2100-01-01'
Select
case
when year(@d)%400=0 then 'yes'
when year(@d)%100=0 then 'no'
when year(@d)%4=0 then 'yes'
else 'no'
end
as is_leap_year
[/code]

Madhivanan

Failing to plan is Planning to fail

rauof_thameem
Starting Member

31 Posts

Posted - 2007-05-16 : 00:46:07

Thanks Mahadi

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-05-16 : 00:56:48

[code]
select
YEAR,
IS_LEAP_YEAR =
-- If 60th day of the year is in February, then it's leap year.
case month(dateadd(yy,a.YEAR-1900,0)+59) when 2 then 'yes' else 'no' end
from
(
select YEAR = 1800 union all
select YEAR = 1900 union all
select YEAR = 2000 union all
select YEAR = 2001 union all
select YEAR = 2002 union all
select YEAR = 2003 union all
select YEAR = 2004 union all
select YEAR = 2005 union all
select YEAR = 2006 union all
select YEAR = 2007 union all
select YEAR = 2008 union all
select YEAR = 2009 union all
select YEAR = 2010 union all
select YEAR = 2011 union all
select YEAR = 2400
) a

Results:

YEAR IS_LEAP_YEAR
----------- ------------
1800 no
1900 no
2000 yes
2001 no
2002 no
2003 no
2004 yes
2005 no
2006 no
2007 no
2008 yes
2009 no
2010 no
2011 no
2400 yes

(15 row(s) affected)

[/code]

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-16 : 10:58:17

Good Logic. I knew you will come up with different logic

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-16 : 11:03:42

quote:
Originally posted by rauof_thameem

Thanks Mahadi

Check my name carefully

Madhivanan

Failing to plan is Planning to fail

anki0121
Starting Member

1 Post

Posted - 2009-04-03 : 04:06:48

This is very simple....
select (case to_number(to_char((trunc(to_date('15/08/08','dd/mm/yy'),'yy')+59),'mm')) when 2 then 'Yes' else 'No' end) as yr
from dual

here I have first found the first day of the year
which can be done by using
Select Trunc(To_date(SYSDATE),'yy')
from dual

and then adding 59 days to it and then using the case statement to check whether the month is 2 or 3 depending on which leap year can be found out

When u need someone...
Ankeyzzzz always dere

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-03 : 05:08:58

quote:
Originally posted by anki0121

This is very simple....
select (case to_number(to_char((trunc(to_date('15/08/08','dd/mm/yy'),'yy')+59),'mm')) when 2 then 'Yes' else 'No' end) as yr
from dual

here I have first found the first day of the year
which can be done by using
Select Trunc(To_date(SYSDATE),'yy')
from dual

and then adding 59 days to it and then using the case statement to check whether the month is 2 or 3 depending on which leap year can be found out

When u need someone...
Ankeyzzzz always dere

Are you aware that this site is specific to MS SQL Server?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources