Click here to Skip to main content
15,915,513 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hello

As a beginner in sql world currently I'm using sql 2005. As a practice session I create a table named PRACTICE consisting some years which is given below:----

4/1/1995 12:00:00 AM 4/1/1995 12:00:00 AM
4/2/1995 12:00:00 AM 5/1/1995 12:00:00 AM
5/15/1995 12:00:00 AM 5/30/1995 12:00:00 AM
6/1/1995 12:00:00 AM 6/30/1995 12:00:00 AM
7/1/1995 12:00:00 AM 9/2/1995 12:00:00 AM
9/3/1995 12:00:00 AM 1/17/1996 12:00:00 AM

So I wanna find out which year is a leap year from that particular table. As a beginner I couldn't find out any query which will show me the last entry year i.e. 1996 as a leap year. so here I'm to find out query from the sql masters.

Thanks...
Posted

Here is the algorithm: http://en.wikipedia.org/wiki/Leap_year#Algorithm[^].

Next step is the query…

—SA
 
Share this answer
 
I sure hope you stored them as DATETIME and not as characters strings.

Read the documentation on the DATEPART function, you can very easily extract the years then perform some reasonably simple tests on them.
 
Share this answer
 
hi, try this...
SQL
CREATE FUNCTION fnLastLeapYear
(
     @Year INT = 0
)
RETURNS INT
AS
BEGIN
    DECLARE @LastLeapYear int = 0
    ;WITH LastLeapYear([year], Found) as
    (
        SELECT
               @Year [year]
             , CASE
                   WHEN datepart (DAY, dateadd(DAY, -1, dateadd(MONTH, 1, convert(DATETIME, convert(VARCHAR, @Year) + '-02-01')))) = 29 THEN
                       'y'
                   ELSE
                       'n'
               END AS Found

        UNION ALL

        SELECT
               [year] - 1 AS [year]
             , CASE
                   WHEN datepart (DAY, dateadd(DAY, -1, dateadd(MONTH, 1, convert(DATETIME, convert(VARCHAR, [year] - 1) + '-02-01')))) = 29 THEN
                       'y'
                   ELSE
                       'n'
               END AS Found
        FROM
            LastLeapYear
        WHERE
            Found = 'n'
       )
    SELECT @LastLeapYear = [year] FROM LastLeapYear WHERE Found = 'y'
    Return @LastLeapYear;
END

call function
SQL
SELECT  dbo.fnLastLeapYear(1995)

Happy Coding!
:)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900