SQL 计算西方一些特殊节日

原创 2011年10月13日 10:36:10

/*
2011.3.23
this can be stored in file system or data table.
*/
DECLARE @x_SPEC XML
SET @x_SPEC =
    '
<root>
    <item>
        <id>1</id>
        <key>Passover</key>
        <refKey></refKey>
        <script>
        DECLARE @y INT
            ,@hy INT
            ,@mat INT
            ,@leap INT
            ,@fday NUMERIC(10,2)
            ,@ffday NUMERIC(10,2)
            ,@dow INT
            ,@cent INT
            ,@leapExp INT
            ,@day INT
            ,@mo INT
            ,@PassoverDay DATETIME
        SET @y=@year
        SET @hy=@y + 3760
        SET @mat = (12 * @hy + 17) %19
        SET @leap = @hy %4
        SET @fday = 32 + 4343 / 98496.0 + @mat + @mat * (272953 / 492480.0) + @leap / 4.0
        SET @fday = @fday - @hy * (313 / 98496.0)
        SET @ffday = @fday - CAST(@fday AS INT)
        SET @dow = (3 * @hy + 5*@leap + CAST(@fday AS INT) + 5) % 7

        IF @dow IN (2,4,6)
            SET @fday = @fday + 1
        ELSE IF @dow=1 AND @mat>6 AND @ffday>= 1367 / 2160.0
            SET @fday = @fday + 2
        ELSE IF @dow=0 AND @mat>11 AND @ffday>=23269 / 25920.0
            SET @fday = @fday + 1

        SET @cent = CAST(@y/100.0 AS INT)
        SET @leapExp = CAST((3 * @cent - 5)/4.0 AS INT)
        IF @y > 1582
            SET @fday = @fday + @leapExp


        SET @day = CAST(@fday AS INT)
        SET @mo = 3

        IF @day>153
        BEGIN
            SET @mo = 8
            SET @day = @day - 153
        END
        ELSE IF @day>122
        BEGIN
            SET @mo = 7
            SET @day = @day -122
        END
        ELSE IF @day > 92
        BEGIN
            SET @mo = 6
            SET @day = @day - 92
        END
        ELSE IF @day > 61
        BEGIN
            SET @mo = 5
            SET @day = @day - 61
        END
        ELSE IF @day > 31
        BEGIN
            SET @mo = 4
            SET @day = @day - 31
        END

        SET @PassoverDay = RTRIM(@y) + ''-''+RTRIM(@mo) + ''-'' + RTRIM(@Day)
        SET @outDay = @PassoverDay
        </script>
    </item>
    <item>
        <id>2</id>
        <key>Easter</key>
        <refKey></refKey>
        <script>
        DECLARE @y INT
            ,@cent INT
            ,@i INT
            ,@j INT
            ,@k INT
            ,@met INT
            ,@emo INT
            ,@eday INT
            ,@Easter DATETIME
        SET @y=@year
        SET @cent = @y /100
        SET @met = @y % 19
        SET @k = (@cent - 17) / 25
        SET @i = (@cent - @cent / 4 - (@cent - @k)/3 + 19 * @met + 15)%30
        SET @i = @i - (@i/28) * (1 - (@i/28) * (29/(@i+1)) * ((21-@met)/11))
        SET @j = (@y + @y/4 + @i + 2 - @cent + @cent/4 ) % 7
        SET @emo = 3 + (@i-@j + 40) / 44
        SET @eday = @i - @j + 28 - 31 * (@emo / 4)

        SET @Easter = RTRIM(@y) + ''-''+RTRIM(@emo) + ''-'' + RTRIM(@eday)
        SET @outDay = @Easter
        </script>
    </item>
    <item>
        <id>3</id>
        <key>OEaster</key>
        <refKey></refKey>
        <script>
        DECLARE @y INT
            ,@i INT
            ,@j INT
            ,@met INT
            ,@emo INT
            ,@eday INT
            ,@leap INT
            ,@OEaster DATETIME
           
        SET @y = @year
        SET @leap = @y /100 - @y / 400 -2
        SET @met = @y % 19
        SET @i = (19 * @met + 15) % 30
        SET @j = (@y + @y /4 + @i) % 7
        SET @emo = 3 + (@i - @j + 40) / 44
        SET @eday = @i - @j + 28 - 31 * (@emo /4)

        SET @OEaster = RTRIM(@y) + ''-''+RTRIM(@emo) + ''-'' + RTRIM(@eday)
        SET @outDay = @OEaster
        </script>
    </item>
    <item>
        <id>4</id>
        <key>Chanukah</key>
        <refKey>Passover</refKey>
        <script>
        DECLARE @y INT
            ,@days INT
            ,@PassoverD1 DATETIME
            ,@PassoverD2 DATETIME
            ,@Chanukah DATETIME
            ,@sql NVARCHAR(2000)
            ,@itXml XML
            ,@itKey VARCHAR(40)
           
        SET @y=@year
        SET @itXML = @x
        SET @itKey = @key
       
        SET @sql=  @itXml.value(''(//item[key=sql:variable("@itKey")]/script)[1]'',''NVARCHAR(2000)'')
        EXEC sp_executeSQL @sql,N''@year INT,@outDay DATETIME OUT'',@y,@PassoverD1 OUT
       
        SET @y = @y + 1
        EXEC sp_executeSQL @sql,N''@year INT,@outDay DATETIME OUT'',@y,@PassoverD2 OUT
       
        SET @days = DATEDIFF(dd,@PassoverD1,@PassoverD2)
       
        IF @days IN (355, 385)
            SET @outDay = DATEADD(dd,246,@PassoverD1)
        ELSE
            SET @outDay = DATEADD(dd,245,@PassoverD1)
       
        </script>
    </item>
    <item>
        <id>5</id>
        <key>TishaBAv</key>
        <refKey>Passover</refKey>
        <script>
        DECLARE @y INT
            ,@dw INT
            ,@Passover DATETIME
            ,@sql NVARCHAR(2000)
            ,@itXml XML
            ,@itKey VARCHAR(40)
                   
        SET @y=@year
        SET @itXML = @x
        SET @itKey = @key
               
        SET @sql=  @itXml.value(''(//item[key=sql:variable("@itKey")]/script)[1]'',''NVARCHAR(2000)'')
        EXEC sp_executeSQL @sql,N''@year INT,@outDay DATETIME OUT'',@y,@Passover OUT

        SET @dw = DATEPART(DW,@Passover)
        IF @dw = 7
            SET @outDay = DATEADD(dd,113, @Passover)
        ELSE
            SET @outDay = DATEADD(dd,112, @Passover)
        </script>
    </item>
    <item>
        <id>6</id>
        <key>TuBishvat</key>
        <refKey>Passover</refKey>
        <script>
        DECLARE @y INT
            ,@days INT
            ,@PassoverD1 DATETIME
            ,@PassoverD2 DATETIME
            ,@sql NVARCHAR(2000)
            ,@itXml XML
            ,@itKey VARCHAR(40)
                   
        SET @y=@year
        SET @itXML = @x
        SET @itKey = @key

        SET @y = @y - 1
        SET @sql=  @itXml.value(''(//item[key=sql:variable("@itKey")]/script)[1]'',''NVARCHAR(2000)'')
        EXEC sp_executeSQL @sql,N''@year INT,@outDay DATETIME OUT'',@y,@PassoverD1 OUT
        SET @y = @year
        EXEC sp_executeSQL @sql,N''@year INT,@outDay DATETIME OUT'',@y,@PassoverD2 OUT

        SET @days = DATEDIFF(dd,@PassoverD1,@PassoverD2)
        IF @days > 355
            SET @outDay = DATEADD(dd,-89, @PassoverD2)
        ELSE
            SET @outDay = DATEADD(dd,-59, @PassoverD2)
        </script>
    </item>
    <item>
        <id>7</id>
        <key>YomHaAtzmaut</key>
        <refKey>Passover</refKey>
        <script>
        DECLARE @y INT
            ,@dw INT
            ,@Passover DATETIME
            ,@sql NVARCHAR(2000)
            ,@itXml XML
            ,@itKey VARCHAR(40)
                   
        SET @y=@year
        SET @itXML = @x
        SET @itKey = @key

        IF @y = 2004
            SET @outDay = CONVERT(DATETIME,''2004-04-27'',120)
        ELSE
        BEGIN
            SET @sql=  @itXml.value(''(//item[key=sql:variable("@itKey")]/script)[1]'',''NVARCHAR(2000)'')
            EXEC sp_executeSQL @sql,N''@year INT,@outDay DATETIME OUT'',@y,@Passover OUT
            SET @dw = DATEPART(DW,@Passover)
            IF @dw = 1
                SET @outDay = DATEADD(dd,18,@Passover)
            ELSE IF @dw = 7
                SET @outDay = DATEADD(dd,19,@Passover)
            ELSE
                SET @outDay = DATEADD(dd,20,@Passover)
        END
        </script>
    </item>
</root>   
    '

DECLARE @t_res TABLE(
    hType VARCHAR, --F,M,S,O
    yy INT,
    hKey VARCHAR(40),
    hDate DATETIME,
    wDay AS DATEPART(DW,hDate)
)

DECLARE @key VARCHAR(40)
    ,@refKey VARCHAR(40)
    ,@year INT
    ,@sql NVARCHAR(2000)
    ,@hDay DATETIME

SET @year= 2010  --param from outside

/*
INSERT holidays SPEC
*/
DECLARE @i INT, @cnt INT
SELECT @i = 1, @cnt = @x_SPEC.value('count(//key)','INT')

WHILE @i<= @cnt
BEGIN
    SELECT    @sql=T.x.value('script[1]','NVARCHAR(2000)') ,
            @key = T.x.value('key[1]','VARCHAR(40)'),
            @refKey = T.x.value('refKey[1]','VARCHAR(40)')
        /*FROM @x_SPEC.nodes('root/item[position()=sql:variable("@i")]') AS T(x)*/
        FROM @x_SPEC.nodes('root/item[id=sql:variable("@i")]') AS T(x)

    IF @refKey = ''
        EXEC sp_executeSQL @sql,N'@year INT,@outDay DATETIME OUT',@year,@hDay OUT
    ELSE
        EXEC sp_executeSQL @sql,N'@year INT,@x XML,@key VARCHAR(40),@outDay DATETIME OUT',@year,@x_SPEC,@refKey,@hDay OUT

    INSERT @t_res(hType,yy,hKey,hDate) SELECT 'S',@year,@key,@hDay

    SET @i=@i+1
END

SELECT * FROM @t_res

 

 

 

hType yy          hKey                                     hDate                   wDay
----- ----------- ---------------------------------------- ----------------------- -----------
S     2010        Passover                                 2010-03-30 00:00:00.000 3
S     2010        Easter                                   2010-04-04 00:00:00.000 1
S     2010        OEaster                                  2010-03-22 00:00:00.000 2
S     2010        Chanukah                                 2010-12-01 00:00:00.000 4
S     2010        TishaBAv                                 2010-07-20 00:00:00.000 3
S     2010        TuBishvat                                2010-01-30 00:00:00.000 7
S     2010        YomHaAtzmaut                             2010-04-19 00:00:00.000 2
S     2010        YomHaAtzmaut                             2010-04-19 00:00:00.000 2

 

 

网络工程师系统可靠性计算强化训练教程

网络工程师考试考察知识点繁多,形式多样。如何有效把握每种考察形式,拿到相应分数?这是历年考生最挠头的事情。本系列课程紧抓考生痛点,对网工考试中重点题型分门别类讲解,反复强化训练,助力考生查缺补漏,拿到相应分数。本次分课程重点讲解了系统可靠性的计算方法。通过基本概念阐述->历年真题强化训练->独家解题技巧总结,三大步骤帮助考生掌握系统可靠性的计算方法,拿到相应分数。
  • 2016年07月11日 09:56

一些特殊功能的SQL语句(特殊SQL)

如今不管是MySql还是Oracle,都有很多客户端软件方便我们去操作它,MySql有Front,Sqlyog,Oracle有toad,PL/Sql,但有一些操作还是使用命令方便些,如下所示: >>...
  • shanliangliuxing
  • shanliangliuxing
  • 2012-03-28 16:55:04
  • 846

显示特殊节日脚本.rar显示特殊节日脚本.rar

  • 2010年03月26日 08:56
  • 2KB
  • 下载

纯JAVA计算日期的农历节日、公历节日代码!

  • 2011年12月22日 17:38
  • 19KB
  • 下载

JAVA计算日期的农历节日、公历节日代码

  • 2016年02月23日 16:01
  • 7KB
  • 下载

asp.net2.0里用Calendar控件做带节日提示的日历

1、建立网页CalendarThree.aspx,代码如下:@ Page Language="C#" AutoEventWireup="true" CodeFile="CalendarThree.as...
  • aspnet2002web
  • aspnet2002web
  • 2007-09-02 21:54:00
  • 1549

日历控件,带各种节日和星期

  • 2012年02月16日 08:44
  • 50KB
  • 下载

农历、节气、节日算法

[java] view plain copy /**   * @(#)Lunar.java 2008-4-5 2:06:22   */   package luoz...
  • qq_18944923
  • qq_18944923
  • 2016-09-21 16:32:40
  • 1011

asp.net2.0里用Calendar控件做带节日提示的日历

1、建立网页CalendarThree.aspx,代码如下:@ Page Language="C#" AutoEventWireup="true" CodeFile="CalendarThree.as...
  • zzkshow
  • zzkshow
  • 2007-12-06 15:37:00
  • 565

MySQL十条特殊技巧

 MySQL易学易用,附带丰富的技术文档,这两个因素使之被广泛应用。然而,随着MySQL发展加快,即使一个MySQL老手有时也会为该软件出其不意的功能感叹。本文将为你介绍这些不为人知的特性。  以XM...
  • zhouminghong
  • zhouminghong
  • 2007-08-21 10:18:00
  • 558
收藏助手
不良信息举报
您举报文章:SQL 计算西方一些特殊节日
举报原因:
原因补充:

(最多只允许输入30个字)