# SQL 计算西方一些特殊节日

/*
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)
ELSE

</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
ELSE
</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
ELSE
</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
ELSE IF @dw = 7
ELSE
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

• 本文已收录于以下专栏：

举报原因： 您举报文章：SQL 计算西方一些特殊节日 色情 政治 抄袭 广告 招聘 骂人 其他 (最多只允许输入30个字)