如何从年份列表中计算闰年 [英] How do I calculate leap year from a list of years

问题描述

你好



作为sql世界的初学者，我现在正在使用sql 2005.作为练习课程，我创建了一个名为PRACTICE的表，其中包含了几年的时间。下面：----



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



所以我想知道哪一年是特定表格的闰年。作为初学者，我无法找到任何可以向我显示最后一年，即1996年为闰年的查询。所以在这里我要找出来自sql master的查询。



谢谢...

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...

推荐答案

以下是算法： http://en.wikipedia.org/wiki/Leap_year#Algorithm [ ^ ]。



下一步是查询...

-SA

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

Next step is the query…

—SA

我当然希望你将它们存储为DATETIME而不是字符串。



阅读DATEPART函数的文档，您可以非常轻松地提取年份，然后对它们执行一些相当简单的测试。

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.

试试这个...

try this...

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

SELECT  dbo.fnLastLeapYear(1995)

Happy Coding！

：）

Happy Coding!
:)

这篇关于如何从年份列表中计算闰年的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！