Leap year code - 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 2008 Forums

Transact-SQL (2008)

Leap year code

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2009-07-22 : 10:59:55

Hi There,

Just a quick one,

will the below code logic properly determine a leap year?


WHEN month(@StartDate) = 2 AND YEAR(@StartDate) % 4 = 0 THEN 29
WHEN month(@StartDate) = 2 AND YEAR(@StartDate) % 4 = 1 THEN 28

The paramters are for reporting services but that's neither here or there:) It's to find out specifically if the last part:


YEAR(@StartDate) % 4 = 0 THEN 29

Will work?

Hope someone can help

Cheers

mfemenel
Professor Frink

1421 Posts

Posted - 2009-07-22 : 11:28:00

it would also be a leap year if divisible by 400 or 100.

declare @date datetime
set @date='4/1/2999'

select case when cast(datepart(yy,@date) as smallint) % 4=0 OR
cast(datepart(yy,@date) as smallint) % 100=0 OR
cast(datepart(yy,@date) as smallint) % 400=0
then 1 else 0 end

Mike
"oh, that monkey is going to pay"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-22 : 11:28:56

You logic has a bug in it because years that are divisible by 4 that are not evenly divisible by 400 are not leap years: 1700, 1800, 1900, 2100.

The following code works for any datetime value:


select
	IsLeapYear =
	case datepart(dy,dateadd(yy,datediff(yy,-1,DT),-1)) when 366 then 1 else 0 end,
	DT
from
	( --Test data
	select DT = getdate() union all
	select DT = '20080101' union all
	select DT = '20011130' union all
	select DT = '19001205' union all
	select DT = '20000517' union all
	select DT = '21001130' union all
	select DT = '20000717'
	) a

Results:


IsLeapYear  DT
----------- -------------------------
0           2009-07-22 11:27:44.570
1           2008-01-01 00:00:00.000
0           2001-11-30 00:00:00.000
0           1900-12-05 00:00:00.000
1           2000-05-17 00:00:00.000
0           2100-11-30 00:00:00.000
1           2000-07-17 00:00:00.000

(7 row(s) affected)

If you are just after the last day of the month, this will do it:

select LastDayofMonth = dateadd(mm,datediff(mm,-1,@StartDate)),-1)

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-22 : 11:37:11

Here are a couple of suggestions.
http://weblogs.sqlteam.com/peterl/archive/2009/02/25/Fastest-LeapYear-checker.aspx

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-22 : 11:44:40

SELECT ISDATE(10000 * DATEPART(YEAR, @StartDate) + 229) AS IsLeapYear

N 56°04'39.26"
E 12°55'05.63"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-22 : 12:11:09

quote:
Originally posted by Peso

SELECT ISDATE(10000 * DATEPART(YEAR, @StartDate) + 229) AS IsLeapYear

N 56°04'39.26"
E 12°55'05.63"

I havn't tested it, but my guess is the implicit conversion to a text string in the ISDATE function, and evaluation of that string will be slower than the pure date function methods.

Other variations with ISDATE:

select isdate(10000*year(@StartDate)+229) AS IsLeapYear
select isdate(datename(year,@StartDate)+'0229') AS IsLeapYear,

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-22 : 12:31:32

I couldn't resist doing a test

DECLARE	@theDate DATETIME,
	@IsLeapYear TINYINT,
	@Timer DATETIME

SELECT	@theDate = '17530101',
	@Timer = GETDATE()

WHILE @theDate < '99991231'
	BEGIN
		-- 9600 ms
--		SET	@IsLeapYear = ISDATE(10000 * DATEPART(YEAR, @theDate) + 229)
		-- 9200 ms
--		SET	@IsLeapYear = ISDATE(DATENAME(YEAR, @theDate) + '0229')
		-- 6200 ms
--		SET	@IsLeapYear = case datepart(dy,dateadd(yy,datediff(yy,-1,@theDate),-1)) when 366 then 1 else 0 end
		-- 5800 ms
--		SET	@IsLeapYear =CASE DATEPART(DAY, DATEADD(YEAR, DATEPART(YEAR, @theDate) - 1904, '19040229')) WHEN 29 THEN 1 ELSE 0 END
		-- 6400 ms
--		SET	@IsLeapYear = CASE DATEPART(MONTH, DATEADD(DAY, 59, DATEADD(YEAR, DATEPART(YEAR, @theDate) - 1900, 0))) WHEN 3 THEN 0 ELSE 1 END

		-- 2800 ms with only loop, as baseline
		SET	@theDate = DATEADD(DAY, 1, @theDate)
	END

SELECT	DATEDIFF(MILLISECOND, @Timer, GETDATE())

N 56°04'39.26"
E 12°55'05.63"

cidr
Posting Yak Master

207 Posts

Posted - 2009-08-11 : 05:09:40

Thanks for all your help folks. this made sense and will help me for the future.

Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources