Peter Larsson Blog

Patron Saint of Lost Yaks

Fastest LeapYear checker?

by Peter Larsson in  , , , ,

CREATE FUNCTION dbo.fnIsLeapYear
(
            @Year SMALLINT
)
RETURNS BIT
AS
BEGIN
            RETURN      CASE DATEPART(DAY, DATEADD(YEAR, @Year - 1904, '19040229'))
                                     WHEN 29 THEN 1
                                     ELSE 0
                         END
END

Legacy Comments
Brian Tkatch
2009-02-25		 re: Fastest LeapYear checker?
Here's one that is very similar. The difference in timing is not much.

CREATE FUNCTION Is_Leap_Year(@Year VARCHAR(4))
RETURNS BIT
AS
BEGIN
RETURN MONTH(CAST(@Year + '/2/28' AS DATETIME) + 1) - 3
END;
GO
James Curran
2009-02-25		 re: Fastest LeapYear checker?
Should that be

DATEADD(YEAR, @Year - 1904, ...

If @Year == 1900, then you have
DATEADD(YEAR, 0, '19040229'))

which will tell you that 1900 was a leap year, when it was not.
Adam Machanic
2009-02-25		 re: Fastest LeapYear checker?
Here's another version that starts with an input date (and it does work for 1900 <g>):

DECLARE @start_date DATETIME
SET @start_date = '19000201'

SELECT
CASE DATEPART(dd, DATEADD(mm, 2, DATEADD(yy, DATEDIFF(yy, 0, @start_date), 0)) - 1)
WHEN 29 THEN 1
ELSE 0
END
Peso
2009-02-25		 re: Fastest LeapYear checker?
Yes, it should read @Year - 1904.
Thank you for the correction.
Peso
2009-02-25		 re: Fastest LeapYear checker?
I thought of that too Adam, but is has 4 functions. My suggestion has only two functions.

Here is another suggestion with 3 functions.

CREATE FUNCTION dbo.fnIsLeapYear2
(
@Year SMALLINT
)
RETURNS BIT
AS
BEGIN
RETURN CASE DATEPART(MONTH, DATEADD(DAY, 59, DATEADD(YEAR, @Year - 1900, 0)))
WHEN 3 THEN 0
ELSE 1
END
END
Brian Tkatch
2009-02-25		 re: Fastest LeapYear checker?
A slight modification of my earlier post. There is a day of year function, why not use it:

CREATE FUNCTION Is_Leap_Year(@Year VARCHAR(4))
RETURNS BIT
AS
BEGIN
RETURN DATEPART(y, CAST(@Year + '/3/1' AS DATETIME)) - 60;
END;
GO
Brian Tkatch
2009-02-25		 re: Fastest LeapYear checker?
Minor correction. No reason for the explicit CAST, DATEPART will handle that for us:

CREATE FUNCTION Is_Leap_Year(@Year VARCHAR(4))
RETURNS BIT
AS
BEGIN
RETURN DATEPART(y, @Year + '/3/1') - 60;
END;
Uri Dimant
2009-02-26		 re: Fastest LeapYear checker?
Hi , this is another one:-)
create function dbo.fn_IsLeapYear (@year int)
returns bit
as
begin
return(select case datepart(mm, dateadd(dd, 1, cast((cast(@year as varchar(4)) + '0228') as datetime)))
when 2 then 1
else 0
end)
end
go
Hugo Kornelis
2009-02-26		 re: Fastest LeapYear checker?
Here's a completely different method. I have no idea if it's faster or quicker.

CREATE FUNCTION Is_Leap_Year(@Year int)
RETURNS bit
AS
BEGIN
RETURN CASE
WHEN @Year % 400 = 0
OR ( @Year % 4 = 0
AND @Year % 100 <> 0) THEN 1
ELSE 0
END;
END;
Remote DBA
2009-02-26		 re: Fastest LeapYear checker?
I would not concentrate on 2/29 , we now that every forth year is leap year so why not just check the @year % 4?
Frank Kalis
2009-02-26		 re: Fastest LeapYear checker?
...and yet another one:

CREATE FUNCTION dbo.Is_Leap_Year(@Year int)
RETURNS bit
AS
BEGIN
RETURN ISDATE (@year * 10000 + 229)
END;
Peso
2009-02-26		 re: Fastest LeapYear checker?
I like that one!
Brian Tkatch
2009-02-26		 re: Fastest LeapYear checker?
@Frank

Sheesh, i didn't know about ISDATE(). But why * by 10000?
CREATE FUNCTION Is_Leap_Year(@Year VARCHAR(4))
RETURNS BIT
AS
BEGIN
RETURN ISDATE(@Year + '0229');
END;

@RemoteDBA The current rule is, every four years is a leap year, unless it is a hundreds year. So, 1900 was not a leap year. Unless, the hundreds year is divisible by 400, such as 2000 which was a leap year.

As the rule may change when people decide to fix the calendar, it is better to use the built-in date functionality.
Brian Tkatch
2009-02-26		 re: Fastest LeapYear checker?
My last submission is missing the date separators. They are required for things like year 1 to work. Though, it isn't a SQL Server supported DATE anyway.

Regardless, all mine are bad anyway, resulting in errors or wrong answers when DATEFORMAT is changed. Everyone else's works. That is, the original submission, peso, uri, hugo, and frank's all work with the DATEFORMAT changed to each of its six values. Mine do not.

I learnt something from this exercise, thanx!
Brian Tkatch
2009-02-26		 re: Fastest LeapYear checker?
More comments.

If the year is less than 4 characters, all FUNCTIONs only Hugo's FUNCTION works. The other throw conversion errors.

If the year has four characters, but is outside SQL Server's range, it returns an out of range error.

I would suggest that the FUNCTIONs do bound checking, or use Hugo's crude method.
Frank Kalis
2009-02-26		 re: Fastest LeapYear checker?
Brian,

good point about the * 10000. I copied Hugo's function where the @year parameter happened to be of int data type. And in order to get this running with ints you need to get this into the YYYYMMDD format which is @year * 10000 + the rest.

You changed it to varchar(4), so you just need to concatenate both parts which is just fine as well. I really like such collaborations. :-)

Btw, I might be wrong but SQL Server 2008 DOES support the ANSI-SQL date range which spans from 0001 to 9999, if I am not completely mistaken.
--
Cheers,
Frank
Brian Tkatch
2009-02-26		 re: Fastest LeapYear checker?
@Frank

Silly me. I'm playing with 2005 Express, so i'm stuck with the limitation of 1753-9999. I should have mentioned the version earlier.

In any case, going less than 4 characters may cause a problem, because the routines here rely on position, to some extent.

In 2005 Express, DATEFORMAT is SET at runtime, and that can break any CAST statement with DATETIME as well, because the routine has no control over it. The DATEPART() usage is much better, but i wonder if relying on a base date of 1900 is such a good idea. That is, will converting 0 to a date always be 1900?
Frank Kalis
2009-02-27		 re: Fastest LeapYear checker?
" but i wonder if relying on a base date of 1900 is such a good idea. That is, will converting 0 to a date always be 1900?"

Yes. To the best of my understanding everything in SQL Server's date arithmetics is based on the base date '1900-01-01' = 0.

This is made official by being mentioned in the Books Online:

"Values with the datetime data type are stored internally by the SQL Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900. The base date is the system reference date...."

This is taken from the SQL Server 2005 BOL and I think it always was and still is true. Someone else may correct me if I'm wrong.
Brian Tkatch
2009-02-27		 re: Fastest LeapYear checker?
@Frank Excellent. If its documented, its reliable upon.

Except for the ISDATE() documentation. Two of their samples fail when DATEFORMAT is changed. I emailed in the correction. :)
Joe Celko
2009-03-03		 re: Fastest LeapYear checker?
Whata about a data driven version with error handling?

CREATE FUNCTION Is_Leap_Year(@in_year INTEGER)
RETURNS INTEGER
AS
RETURN (
CASE WHEN @in_year NOT BETWEEN 0001 AND 9999
THEN -1 -- range error
WHEN @in_year
IN (SELECT leap_year
FROM VALUES (0004, 0008, etc.)
AS LeapYearList(leap_year
THEN 1 ELSE 0 END);




END;
Mark
2009-10-03		 re: Fastest LeapYear checker?

...just for fun


CREATE FUNCTION dbo.Another_Is_Leap_Year(@Year int)
RETURNS bit
AS
BEGIN
RETURN (1-SIGN(@Year % 400)) | (SIGN(@Year % 100) & (1-SIGN(@Year % 4)))
END;